CODEWITHSUNDEEP
mysqlsqldatabaseselect
jaxicab
jaxicab

Reputation: 29

SQL Database Query: Count(Distinct)

I am working on an 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

Upvotes: 2

Views: 91

Answers (2)

giwyni
giwyni

Reputation: 3354

Use the Group by and the where clause like this:

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
where project.PNO LIKE 1
group by project.PNO, project.PNAME 
HAVING COUNT(DISTINCT works_on.ESSN) > 2

Upvotes: 0

Mureinik
Mureinik

Reputation: 311883

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

Upvotes: 2

Related Questions