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!
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