How to use SQL MAX(SUM()) function

Question

I need some help using the SQL max and sum aggregate functions in SQL.

I want to display the first and last names of employees who have put in the most number of total hours on projects. And I want to list the employees with the highest total combined project hours.

I have two tables:

employee: 
FNAMEM--LNAME--SSN--BDATE--ADDRESS--SALARY--SUPERSSN--DNO

works_on:
ESSN--PROJECT_NUM--HOURS

This is what I have so far but there is a syntax error in the code:

select fname, lname, max(sum(hours)) "Total Hours", essn
from employee, works_on
where essn = ssn
order by lname;

I know that max(sum(hours)) does not work, what will give me the right result?

Solution 1

You need to use a group by if you are going to use the aggregate function sum.

Something like this;

SELECT s.fname, s.lname 
FROM (SELECT fname, lname, SUM(w.hours) SumHours, w.project_num 
      FROM Emplyee e 
      JOIN Works_on w ON  w.essn = e.ssn 
      GROUP BY e.fname, e.lname, w.project_num) s
WHERE s.SumHours = (SELECT MAX(w.hours) MaxSum
                    FROM Works_on w1 
                    WHERE  w1.project_num = s.project_num)

Note that subqueries embedded in the where clause invoke a massive performance penalty.

Solution 2

SELECT s.fname, s.lname 
FROM (SELECT fname, lname, SUM(w.hours) SumHours
      FROM employee e 
      JOIN works_on w ON  w.essn = e.ssn 
      GROUP BY e.fname, e.lname) s
WHERE s.SumHours = (SELECT MAX(hours) MaxSum
                    FROM works_on w1);

This code worked for me; Thanks to user: PM 77-1 for putting me on the right track.