Active Employee Count per Department — NULL Propagation in SQL

The problem

Scenario: Helix Systems' HR operations team is planning resource allocation and needs every department paired with its current active-employee count, including departments with zero active employees so understaffed departments remain visible.

Task: Write a query to return each department's id, name, and active-employee count, with the count reported as 0 for departments with no active employees.

Assumptions:

An active employee has is_active equal to TRUE.
A department's active_employee_count is the count of active employees assigned to it.
The result covers every department.
A department with no active employees appears with active_employee_count of 0.

Output:

One row per department.
Columns in this order: department_id, department_name, active_employee_count.
Sorted by department_name ascending.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  d.id AS department_id,
  d.name AS department_name,
  COUNT(e.id) AS active_employee_count
FROM
  departments d
  LEFT JOIN employees e ON e.department_id = d.id
  AND e.is_active = TRUE
GROUP BY
  d.id,
  d.name
ORDER BY
  d.name

The shape

The activity test belongs inside the join condition, not in a WHERE clause, because the LEFT JOIN has to preserve departments with zero active employees. Once those departments survive the join, COUNT(e.id) lands them on 0 automatically — the unmatched row has e.id set to NULL, and COUNT of a NULL column is zero.

Clause by clause

SELECT d.id AS department_id, d.name AS department_name, COUNT(e.id) AS active_employee_count returns one row per department with the count of active employees attached.
FROM departments d LEFT JOIN employees e ON e.department_id = d.id AND e.is_active = TRUE pairs each department with its active employees. The is_active = TRUE condition lives inside the ON clause, which means an inactive employee fails the join condition (the row is dropped) and a department with no active employees fails to find any match (the department row is preserved with NULL on the employees side).
GROUP BY d.id, d.name collapses the per-employee rows back to one row per department.
ORDER BY d.name sorts the report alphabetically by department name.

The trap

Moving e.is_active = TRUE from the join condition to a WHERE clause silently converts the LEFT JOIN into an inner join. The join first preserves the department row with e.is_active set to NULL, then the WHERE evaluates NULL = TRUE as NULL, and the department gets filtered out. The understaffed departments — the exact rows the report is supposed to surface — disappear without warning. Any condition on the right-hand table of a LEFT JOIN that should not eliminate left-side rows must live inside the ON clause.

You practiced left-joining departments to the employee detail with the activity restriction inside the join condition, so empty departments still reach the count step and arrive as 0.

Return each department's `id`, name, and active-employee count, with the count reported as `0` for departments with no active employees

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.