Join table with a subquery + SUM function in mySQL

Question

Here's the prompt and the code I'm using. "FIND THE NAME OF THE FEMALE EMPLOYEES WHOSE WORKING HOURS FOR ALL PROJECTS IS MORE THAN 20 HOURS"

SELECT employee.ssn, employee.first_name, employee.last_name
FROM employee
WHERE employee.sex='F' AND employee.ssn IN (
    SELECT working_hour.ssn
    FROM working_hour
    GROUP BY working_hour.ssn
    HAVING SUM(working_hour.work_hour) > 20)
ORDER BY employee.first_name, employee.last_name;

Now I want the output to include the SUM(working_hour.work_hour) grouped by the the employee.ssn but I can't seem to find the correct cote to excecute this.

PS: Does it have something to do with the working_hour table having a composite key?

Answer 1

If you want to access the fields in the subquery, then I would rephrase this using a join:

SELECT e.ssn, e.first_name, e.last_name, wh.total_hours
FROM employee e
INNER JOIN
(
    SELECT ssn, SUM(work_hour) AS total_hours
    FROM working_hour
    GROUP BY ssn
    HAVING SUM(work_hour) > 20
) wh
    ON wh.ssn = e.ssn
WHERE e.sex = 'F'
ORDER BY e.first_name, e.last_name;