SQL COUNT(*) returning the wrong answer

sql

Solution

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

Problem

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

Original source