CODEWITHSUNDEEP
sql
user1362208
user1362208

Reputation:

SQL COUNT(*) returning the wrong answer

The following script should return the name of the departments and the number of employees that are in those departments, the Marketing,Executive and Sales departments have '0' employees but instead of '0' , the returned value is '1'. How can I correct it?

select Department, Departments.DepartmentID, count(*) as 'NumOfEmps' 
from Departments
left join Employees
on   Employees.DepartmentID = Departments.DepartmentID
group by Departments.DepartmentID,Department

enter image description here

Upvotes: 3

Views: 206

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

Don't use Count(*) count the thing you want to count namely the employees.

Count(*) counts the whole row. Since there's always going to be at least one record for each Department in Departments when you do count(*) you'll always get at least 1 

SELECT d.Department, d.DepartmentID, count(e.EmployeeID)
FROM Departments d
    LEFT JOIN employees e
    ON d.DepartmentID = e.DepartmentID
GROUP BY 
 d.Department, d.DepartmentID

DEMO

Upvotes: 5

Yuck
Yuck

Reputation: 50835

You can't do that all in one query. You need a sub-query to get the employee counts first, then get the related department information (name, etc.) using the aggregated results:

SELECT Department, Departments.DepartmentID, t.NumOfEmps
FROM Departments
LEFT JOIN (SELECT DepartmentID, count(*) as 'NumOfEmps'
           FROM Employees
           GROUP BY DepartmentID) t
  ON t.DepartmentID = Departments.DepartmentID

I'm making some assumptions about your schema since it's not listed. Column names may be off a bit, but this is the general idea. Hope it helps.

Upvotes: 6

Related Questions