jaxicab jaxicab - 7 months ago 12
SQL Question

SQL Database Query: Count(Distinct)

I am working on a sql query to do the following:

"For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on that project."

This is what I have currently for project number 1

SELECT project.PNO, project.PNAME, COUNT(DISTINCT works_on.ESSN)
AS '# of employess worked on'

FROM project INNER JOIN works_on
ON project.PNO = works_on.PNO

INNER JOIN employee
ON works_on.ESSN = employee.SSN

HAVING COUNT(DISTINCT works_on.ESSN) > 2
AND project.PNO LIKE 1


and it returns:

PNO:1

PNAME:ProductX

number of employess worked on: 7

When it should return NULL since project number 1 only has two distinct ESSNs in the works_on table, but instead it is returning the total number of distinct ESSNs in the works_on table

Attached is my schema

ER Diagram

Answer

You need to have there results by project, meaning you're missing a group by clause:

SELECT     project.PNO,
           project.PNAME, 
           COUNT(DISTINCT works_on.ESSN) AS '# of employess worked on'
FROM       project 
INNER JOIN works_on ON project.PNO = works_on.PNO
INNER JOIN employee ON works_on.ESSN = employee.SSN
GROUP BY   project.PNO, project.PNAME -- Here!
HAVING     COUNT(DISTINCT works_on.ESSN) > 2 AND project.PNO LIKE 1