468,242 Members | 1,474 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Multiple Sum and Join

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
1 2327
deepuv04
227 Expert 100+
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.

Similar topics

reply views Thread by Petre Agenbag | last post: by
4 posts views Thread by Bob Hotschins | last post: by
2 posts views Thread by sjoshi | last post: by
2 posts views Thread by narendra vuradi | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.