MiddleCodeCommonNot answered yet
Count employees per department, including empty ones
Return each department's name and its employee count — departments with zero staff must show 0.
-- Departments(id, name)
-- Employee(id, name, salary, dept_id, manager_id)
-- write your query hereWrite the query.
Left-join from departments and count the joined key: SELECT d.name, COUNT(e.id) FROM Departments d LEFT JOIN Employee e ON e.dept_id = d.id GROUP BY d.id, d.name. Use COUNT(e.id) (not COUNT(*)) so an empty department shows 0 — COUNT(*) would count the one NULL-padded row as 1.
- ✗Using
COUNT(*)on aLEFT JOIN, counting the NULL row as 1 - ✗Inner-joining, which drops the empty departments entirely
- ✗Aggregating only
Employee, so zero-staff departments never appear
- →Why does
COUNT(e.id)return 0 butCOUNT(*)return 1 for an empty department? - →What must appear in
GROUP BYwhen you selectd.namealongside the count?
Contents
Task
Return each department's name from Departments with its employee count, showing 0 for empty ones.
Solution
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;Key points
LEFT JOINfromDepartmentsretains departments with no employees (NULL-padded row).COUNT(e.id)ignores NULLs, so an empty department yields0;COUNT(*)would give1.GROUP BYincludes every non-aggregated selected column (d.id,d.name).
Contents
