user7157554 user7157554 - 20 days ago 7
MySQL Question

mysql select sum anually for multiple users

I am somehow stuck with this one:
I have a table with mutliple payments which have a date and a staff_id.
I want to get the sum of the payments for every staff_id for every year.

For example:

2011 - Staff1 - 66$
2011 - Staff2 - 24$
2012 - Staff1 - 86$
2012 - Staff2 - 0$


My idea was something like:
SELECT staff_id, YEAR(p.payment_date), sum(p.amount) FROM payment p WHERE staff_id = (SELECT p.staff_id FROM payment p GROUP BY p.staff_id)
GROUP BY YEAR(p.payment_date);

This will obviously not work because "(SELECT p.staff_id FROM payment p GROUP BY p.staff_id)" will return more than one value.

How can I do this right?

Cheers!

Answer
SELECT 
    YEAR(p.payment_date), 
    staff_id, 
    sum(p.amount)
FROM 
    payment p 
GROUP BY 
    YEAR(p.payment_date),
    staff_id
Comments