Pulling data from three tables; Attempting to list project number, project name, and hours worked for projects on which the employee is working

Granted this is from an assignment, and the instructions are not clear as to whether I should list an employee identifier, however I am assuming I should (unless someone has another interpretation?).

This is the code I currently have:

select e.ssn, p.pnumber, p.pname, count(hours) hours_worked
from works_on w
inner join project p on w.pno = p.pnumber
inner join employee e on w.essn = e.ssn
group by e.ssn, p.pnumber, p.pname
order by e.ssn, hours_worked desc;

I get a list showing all employees working on each project, but hours_worked is always 1. I’m attempting to count from the hours column in the works_on table but it does not appear to work.

Thank you in advance for the help!

Answer

could be you need sum

select e.ssn, p.pnumber, p.pname, sum(hours) hours_worked
from works_on w
inner join project p on w.pno = p.pnumber
inner join employee e on w.essn = e.ssn
group by e.ssn, p.pnumber, p.pname
order by e.ssn, hours_worked desc;

count is for get the number of not null occurrence of values contained in the column