Employee Count by Department — GROUP BY in SQL

The problem

Helix Systems' HR director is reviewing organisational staffing levels ahead of the annual workforce plan.

Write a query to return each department_id and the number of employees assigned to it.

Assumptions:

The employees table contains every active and former employee at Helix Systems.
department_id links each employee to their department.

Output:

One row per distinct department_id, with columns department_id 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
  department_id,
  COUNT(*) AS employee_count
FROM
  employees
GROUP BY
  department_id

The shape

GROUP BY department_id collapses the employees table into one row per department, and COUNT(*) counts the employees that landed in each one. The department_id column on the employees table is what links each person to their org unit, and grouping by it is what turns a row-per-employee table into a row-per-department headcount.

Clause by clause

SELECT department_id, COUNT(*) AS employee_count returns the department identifier alongside its headcount. department_id is in the SELECT list and in GROUP BY, which is the rule whenever you mix a plain column with an aggregate.
FROM employees reads every active and former employee record.
GROUP BY department_id is the partitioning step. Department 1 ends up with 17 rows in its bucket, department 3 with 11, and so on. COUNT(*) then runs once per bucket.

Why this and not `COUNT(DISTINCT department_id)`

COUNT(DISTINCT department_id) FROM employees would return a single number: how many departments exist. That answers a different question. The HR director needs the size of each department, not the size of the department list. The shape the workforce plan needs is one row per department with its headcount, and that requires GROUP BY.

You practiced grouping a fact table (employees) by its foreign-key column (department) to get a per-parent count. The recurring shape behind every "how many X per Y" question in any one-to-many relationship.

Return each `department_id` and the number of employees assigned to it

The shape

Clause by clause

Why this and not `COUNT(DISTINCT department_id)`

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 COUNT(DISTINCT department_id)

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

Why this and not `COUNT(DISTINCT department_id)`