SQL count和group by不返回计数为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;