Multiple Sum and Join

[Ratamahatta]

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:

[I]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[/I]

[deepuv04]

Hi,
Try the following query.

[code=sql]

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


[/code]

Thanks