如何查询工作时长总和大于20小时的员工统计信息？

Hey there! The key here is that when you want to filter results based on an aggregated value (like the sum of hours in your case), you can't use a WHERE clause—you need the HAVING clause instead, since it operates on grouped data after aggregation.

Here's the modified SQL query that meets your requirement:

SELECT fname AS 'Employee_Name', SUM(hours) AS 'Working hours'
FROM employee e
JOIN works_on w ON e.ssn = w.essn
GROUP BY e.ssn, e.fname
HAVING SUM(hours) > 20;

A couple of quick notes to explain the changes:

• The HAVING SUM(hours) > 20 clause is added at the end to only keep groups where the total working hours are greater than 20. Unlike WHERE, which filters rows before aggregation, HAVING filters the grouped results after the SUM() calculation is done.
• I also added e.fname to the GROUP BY clause. While some databases might allow omitting it if ssn is the primary key of the employee table (guaranteeing each ssn maps to exactly one fname), including it makes the query compatible with more SQL dialects and explicitly clarifies the grouping logic.

内容的提问来源于stack exchange，提问作者Neetika Gupta