Managed vs Unmanaged per Department — Conditional Aggregation in SQL

The problem

Helix Systems' HR team wants to understand the management structure by department:

How many employees in each department have a manager on record (manager_id IS NOT NULL).
How many are at the top of the hierarchy with no manager (manager_id IS NULL).

Write a query to return all three columns per department.

Assumptions:

The employees table contains every active and former employee at Helix Systems.
The conditional counts use IS NULL and IS NOT NULL rather than = — manager_id = NULL would always evaluate to unknown and never match.

Output:

One row per department, with columns department_id, has_manager, and no_manager.

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
  department_id,
  COUNT(
    CASE
      WHEN manager_id IS NOT NULL THEN 1
    END
  ) AS has_manager,
  COUNT(
    CASE
      WHEN manager_id IS NULL THEN 1
    END
  ) AS no_manager
FROM
  employees
GROUP BY
  department_id

The shape

The IS NULL / IS NOT NULL predicates split each department's headcount into people with a manager on record and people without one. Department 1 has 16 employees with a manager and 1 person at the top of the chain; the other departments have one or zero top-of-hierarchy employees each.

Clause by clause

department_id is the grouping column. Each department's rows form one group, and the two conditional counts operate inside each group independently.
COUNT(CASE WHEN manager_id IS NOT NULL THEN 1 END) AS has_manager returns 1 for every row whose manager_id has an actual value. Unmatched rows return NULL from the CASE, and COUNT skips them. The result is the count of employees who report to someone.
COUNT(CASE WHEN manager_id IS NULL THEN 1 END) AS no_manager is the mirror image. The predicate flips and the count tallies the employees at the top of the hierarchy.
FROM employees GROUP BY department_id partitions the rows per department.

The trap

manager_id = NULL does not test "is the value missing." It tests equality against the special NULL marker, and any = comparison against NULL produces NULL itself — which CASE treats as not-matched. Every row falls through to the implicit ELSE NULL, and COUNT tallies zero. Worse, the query runs without error: PostgreSQL accepts the predicate as syntactically valid, it just never matches anything. The result comes back as all-zeroes for no_manager and the bug looks like a data issue rather than a predicate issue.

The rule that fixes it is the N005 rule: a missing-value test uses IS NULL or IS NOT NULL, not = or <>. The CASE WHEN predicate has the same semantics as a WHERE predicate, so the same rule carries through.

You practiced conditional aggregation with IS NULL predicates. The same N005 NULL-test rule carries through into CASE WHEN: manager_id = NULL never matches; manager_id IS NULL is the only correct test.

Return all three columns per department

The shape

Clause by clause

The trap

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