Show sales per employee (staff) and year in sakila db?

I’m kind of stuck on an excercise concerning the sakila db. I want to show sales per employee (staff) and year in mySQL and I’m failing at using the staff_id’s correctly.

This is all I have so far:

SELECT  staff_id,payment_id
FROM staff s
LEFT JOIN payment p USING (staff_id)

Can anyone explain to me how to use the staff_id in both staff and payment to display sales per employee?

Answer

You can use group by and aggregate function sum as follows:

SELECT staff_id, sum(amount) as sales_per_staff 
FROM staff s LEFT JOIN payment p USING (staff_id)
Where ... -- use condition according to your requirement
Group by staff_id;

Advertisement

Answer