By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Multiple Sum and Join

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 4 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
Hi,
Try the following query.

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE TABLE EMPL
  3. (  emplID INT,
  4.    Name VARCHAR(20)
  5. )
  6.  
  7. INSERT INTO EMPL
  8. select 1 , 'Bob' UNION
  9. SELECT 2 , 'James'
  10.  
  11.  
  12. CREATE TABLE Bonus (
  13. empl_id int, month int, Bonus int)
  14.  
  15. INSERT INTO Bonus (
  16. empl_id , month , Bonus)
  17. SELECT 1 , 1 , 1000 UNION
  18. SELECT 2 , 1 , 750 UNION
  19. SELECT 1 , 2 , 500 UNION
  20. SELECT 2 , 2 , 1000 UNION
  21. SELECT 1 , 3 , 250 UNION
  22. SELECT 2 , 3 , 500
  23.  
  24.  
  25. SELECT b.Empl_id,Name,Max(B.month),
  26.         (SELECT Bonus FROM Bonus WHERE Empl_id = B.Empl_id AND [month] = MAX(B.month)) as 'Bonus this month',
  27.         sum(B.bonus) as 'Bonus Earned'
  28. FROM   EMPL INNER JOIN
  29.        Bonus B ON EMPL.emplID = B.Empl_id
  30. GROUP BY B.Empl_id,Name
  31.  
  32.  
  33.  
Thanks
Oct 6 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.