SQL Server 2012 add value of rows with matching ids

debugcn 投稿 Dev

Kevin

I'm doing an assignment on SQL Server 2012 where I have to

7) Create a stored procedure (call it SQL7) which will retrieve the Charity ID and Charity Name and the total of all the contribution amounts that each charity has in the contribution table."

Charity ID and Charity Name are in one table and the second table has CharityID and Total contributions.

I don't know how to add the total contributions that each charity has received and output it. The code I have so far is

create proc SQL7
as
   select distinct 
       dbo.CharityTbl.CharityID, CharityName,
   from 
       dbo.CharityTbl, dbo.ContributionsTbl
   where 
      dbo.CharityTbl.CharityID = dbo.ContributionsTbl.CharityID

Thanks in advance!

zedfoxus

You'll probably need grouping (assuming table1 holds charity information and table2 holds contribution information)

create procedure SQL7
as
  select a.charityid, a.charityname, sum(b.totalcontributions) as totals
  from CharityTbl a
  left join ContributionsTbl b on a.charityid = b.charityid
  group by a.charityid, a.charityname

SQLFiddle example: http://sqlfiddle.com/#!3/67cca and http://sqlfiddle.com/#!3/ca00e

create table charityTbl (charityid int, charityname varchar(100));
insert into charityTbl values (1, 'Red Cross'), (2, 'Doctors without borders');

create table contributionsTbl (charityid int, totalcontributions int);
insert into contributionsTbl values
(1, 100),
(1, 200),
(2, 500);

(Just think that this was a stored procedure and was called with exec SQL7)

 select a.charityid, a.charityname, sum(b.totalcontributions) as totals
  from CharityTbl a
  left join ContributionsTbl b on a.charityid = b.charityid
  group by a.charityid, a.charityname

Result:

| charityid |             charityname | totals |
|-----------|-------------------------|--------|
|         2 | Doctors without borders |    500 |
|         1 |               Red Cross |    300 |

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]