Count employees per department, including empty ones
Tables Departments(id, name) and Employee(id, name, dept_id). Return each department's name and its employee count.
Requirements:
- every department must appear, including those with zero employees
- a department with no employees must show count
0, not1 - watch the edge case where the join produces an all-NULL employee row for an empty department
SELECT d.name AS department, /* count here */ AS employee_count
FROM Departments d
-- your query hereWrite the query.
LEFT JOIN Employee onto Departments so every department survives, GROUP BY d.id, d.name, and use COUNT(e.id) — counting the employee column gives 0 for empty departments, whereas COUNT(*) counts the one all-NULL joined row as 1. An INNER JOIN would drop empty departments.
- ✗Using
COUNT(*)and reporting1for empty departments instead of0 - ✗Using
INNER JOIN, which drops departments with no employees - ✗Counting the department key instead of the employee column
- →Why does
COUNT(e.id)return0butCOUNT(*)returns1for an empty department? - →Which table must be on the left of the
LEFT JOINfor this to work?
Task
Count the number of employees per department, including departments with no employees.
SELECT d.name AS department, COUNT(e.id) AS employee_count
FROM Departments d
LEFT JOIN Employee e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY employee_count DESC;How it works
To include empty departments in the result, make Departments the driving table and attach Employee via a LEFT JOIN. A department with no employees still yields one row — but with the Employee columns as NULL.
The key point is COUNT(e.id), not COUNT(*):
COUNT(e.id)counts only non-NULLvalues of the employee column →0for an empty department;COUNT(*)counts rows → for an empty department that would be one NULL row, i.e. a wrong1.
⚠️ An INNER JOIN will not do here: it would drop departments with no match, and they would never appear in the result.
