SQL COUNT（*）返回错误的答案

Question

以下脚本应返回部门的名称以及这些部门中的雇员人数，市场营销，执行和销售部门的雇员为‘0’，但返回的值为‘1’，而不是‘0’。我该如何纠正？

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

Answer 1

不要使用Count(*)数数您想数数的员工。

Count(*)计算整行。由于在进行计数（*）时，部门中每个部门始终至少会有一个记录，因此您总是会获得至少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

演示