mysql - SQL 计数和分组不返回 0 的计数

Question

在我的情况下，我有一个 Employee 表和一个 Department 表。大多数部门都有员工，但也有少数部门为零员工。

我正在尝试调出所有部门，从大多数员工到没有员工进行排序。

我已经尝试了INNER JOIN和LEFT JOIN，但似乎仍然无法得到我想要的。

SELECT COUNT(Department.DEPT_ID) as total, Department.Name
FROM Department
  INNER JOIN Employee
    ON Department.DEPT_ID = Employee.DEPT_ID
GROUP BY Department.Name
ORDER BY total desc, Department.NAME asc;

但是，这不包括任何员工为零的部门。我也尝试过:

SELECT Department.NAME, IFNULL(COUNT(Department.DEPT_ID), 0) as total
FROM Department
  LEFT JOIN Employee
    ON Employee.DEPT_ID = Department.DEPT_ID
GROUP BY Department.NAME 
ORDER BY total desc, Department.NAME asc;

但是这一次，对于任何员工为零的部门，它会将其值设置为 1，而不是 0，这让我感到困惑。

我做错了什么？

编辑:@vkp 的正确解决方案是:

SELECT Department.NAME, IFNULL(COUNT(Employee.DEPT_ID), 0) as total
FROM Department
  LEFT JOIN Employee
    ON Employee.DEPT_ID = Department.DEPT_ID
GROUP BY Department.NAME 
ORDER BY total desc, Department.NAME asc;

Answer 1

SELECT d.deptname,CASE WHEN e.total IS NULL THEN 0 ELSE e.total END AS total
  FROM dept d
  LEFT JOIN 
       (
         SELECT deptid,COUNT(*) AS total
           FROM emp
         GROUP BY deptid
        ) e
    ON d.deptid = e.deptid
ORDER BY CASE WHEN e.total IS NULL THEN 0 ELSE e.total END DESC;