All Departments with Employee Count (LEFT JOIN)
Return every department with its employee_count, ordered by count descending and department_id.
- Joins
- Aggregation
- Sorting
Interview brief
Understand the request
Headcount reporting manager The monthly department scorecard must report zero—not one—for departments with no employees.
Return every department — INCLUDING departments that have zero employees — alongside the number of employees in each. Return department_id, department_name, employee_count — ordered by employee_count DESC, department_id.
Return
- Return one row per department.
- Show employee_count 0 for empty departments.
Constraints
- Preserve departments with LEFT JOIN.
- Count the nullable matched employee key rather than COUNT(*).
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
departments
department_idINTEGERdepartment_nameVARCHAR(50)
employees
employee_idINTEGERdepartment_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
CRITICAL: COUNT(e.employee_id), NOT COUNT(*). COUNT(*) would return 1 for empty departments because the LEFT JOIN still emits one row with NULLs.
Hint 2
GROUP BY both department_id AND department_name (every non-aggregated SELECT column).
Hint 3
Operations dept (id 50) has 0 employees and MUST appear in the result thanks to LEFT JOIN.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name ORDER BY employee_count DESC, d.department_id;Why this works
The COUNT(column) vs COUNT(*) distinction is the #1 LEFT-JOIN bug. COUNT(*) counts rows; COUNT(col) counts non-NULL values of col. For 'how many matched on the right side', always count a column from the right side.
Success check
The five departments appear once each and the empty department has a zero count.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| department_id | department_name | employee_count |
|---|---|---|
| 10 | IT | 4 |
| 20 | HR | 2 |
| 30 | Finance | 2 |
| 40 | Marketing | 2 |
| 50 | Operations | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics: