Multiple sums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple sums

Author

Topic

Ratamahatta
Starting Member

3 Posts

Posted - 2008-10-04 : 19:51:29

I have two tables i would like to join to show bonus earned this month and total this year.
First table: empl.

emplID | Name
1 | Bob
2 | James

Second table: Bonus.

empl_id | month | Bonus
1 | 1 | 1000
2 | 1 | 750
1 | 2 | 500
2 | 2 | 1000
1 | 3 | 250
2 | 3 | 500

The output im looking for is (bonus month 3):
Name | Bonus this month | Bonus total
Bob | 250 | 1750
James | 500 | 2250

This is the closest i have got and it fails. So if anyone can point me in the right direction i would be very thankful.

Failing query:

select e.name, sum(b.bonus) as total,  sum(b1.bonus) as thismth from (empl as e INNER JOIN bonus as b on e.emplID=b.empl_id) INNER JOIN bonus as b1 on e.emplID=b1.empl_id group by e.name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 01:31:54

[code]SELECT e.Name,
SUM(CASE WHEN month=@Month THEN Bonus ELSE 0 END) AS [Bonus This Month],
SUM(CASE WHEN month<=@Month THEN Bonus ELSE 0 END) AS [Bonus Total]
FROM empl e
INNER JOIN Bonus b
ON b.empl_id=e.emplID
GROUP BY e.Name[/code]

@Month is passed on month value (for your posted output pass @Month=3)

Ratamahatta
Starting Member

3 Posts

Posted - 2008-10-05 : 05:05:39

Hi visakh16, thanks for your help. When i tried your query i got no output so i checked the db and it turned out to be an access db so i guess thats why it wont work. Again, thanks for your effort visakh16.

:-/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 05:38:02

quote:
Originally posted by Ratamahatta

Hi visakh16, thanks for your help. When i tried your query i got no output so i checked the db and it turned out to be an access db so i guess thats why it wont work. Again, thanks for your effort visakh16.

:-/

then suggest you to post it on Access forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources