All Departments with Employee Count (LEFT JOIN)
Return every department with its employee_count, ordered by count descending and department_id.
- Joins
- Aggregation
- Sorting
Exercise 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 department_id, department_name, employee_count in this exact left-to-right order.
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:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.