Employee Count per Department — Correlated Subqueries in SQL

The problem

Helix Systems' HR director needs a headcount summary across every department to review staffing levels.

Write a query to return every department's ID, name, and total employee count.

Assumptions:

A department's employee count is the number of employees linked to that department_id.
Every department must appear, including departments with no employees. Departments with no employees should show a count of 0.

Output:

One row per department, with columns id, name, and employee_count.

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,
  d.name,
  (
    SELECT
      COUNT(*)
    FROM
      employees e
    WHERE
      e.department_id = d.id
  ) AS employee_count
FROM
  departments d

The shape

The headcount belongs to each department row, so \COUNT(*)\ goes inline in the \SELECT\ list as a correlated subquery. For every department, the inner query runs once against \employees\ filtered to that department's \id\, and the result is attached as the third column. A department with no employees gets \0\ from \COUNT(*)\ over an empty set, which is the value the spec asks for.

Clause by clause

\SELECT d.id, d.name, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS employee_count\ returns the department's id and name, then the per-department headcount. The inner \WHERE e.department_id = d.id\ references the outer \d.id\, which is what creates the correlation. \COUNT(*)\ always returns an integer, never NULL, so empty departments show \0\ rather than a missing value.
\FROM departments d\ reads every department. No outer \WHERE\ because the spec requires every department in the output, including the empty ones.

Why this and not \`LEFT JOIN\` plus \`GROUP BY\`

\SELECT d.id, d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name\ returns the same numbers, including \0\ for empty departments. The correlated form is one expression instead of a join, an outer-join condition, and a \GROUP BY\ list. When the only thing the report needs from the related table is one scalar per outer row, the inline subquery is the more direct shape.

You practiced a correlated COUNT(*) against a related table — 0 propagates naturally when no related records match, no COALESCE needed.

Return every department's ID, name, and total employee count

The shape

Clause by clause

Why this and not \`LEFT JOIN\` plus \`GROUP BY\`

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

The shape

Clause by clause

Why this and not \LEFT JOIN\ plus \GROUP BY\

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

Why this and not \`LEFT JOIN\` plus \`GROUP BY\`