Multiple Sum and Join

Ratamahatta · #1 October 4th, 2008, 03:33 PM

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

 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
 

deepuv04 · #2 October 6th, 2008, 06:42 AM

Hi,
Try the following query.

Expand|Select|Wrap|Line Numbers

  
CREATE TABLE EMPL

(  emplID INT,

   Name VARCHAR(20)

)
 
INSERT INTO EMPL

select 1 , 'Bob' UNION

SELECT 2 , 'James'
 
CREATE TABLE Bonus (

empl_id int, month int, Bonus int)
 
INSERT INTO Bonus (

empl_id , month , Bonus)

SELECT 1 , 1 , 1000 UNION

SELECT 2 , 1 , 750 UNION

SELECT 1 , 2 , 500 UNION

SELECT 2 , 2 , 1000 UNION

SELECT 1 , 3 , 250 UNION

SELECT 2 , 3 , 500
 
SELECT b.Empl_id,Name,Max(B.month),

        (SELECT Bonus FROM Bonus WHERE Empl_id = B.Empl_id AND [month] = MAX(B.month)) as 'Bonus this month',

        sum(B.bonus) as 'Bonus Earned'

FROM   EMPL INNER JOIN

       Bonus B ON EMPL.emplID = B.Empl_id

GROUP BY B.Empl_id,Name

Thanks

Multiple Sum and Join

Popular Articles

What is Bytes?