Helix Systems' HR director needs a headcount summary by department, split between total staff and currently active employees.
Write a query to return every department ID, the total number of employees assigned to it, and the count of employees currently active.
Assumptions:
- The
employeestable has one row per employee with adepartment_idand anis_activeflag. - Each
department_idwith at least one employee should appear once. - For each department, the total count covers every employee linked to that
department_id. The active count covers only employees whoseis_activeisTRUE.
Output:
- One row per department, with columns
department_id,total_employees, andactive_employees.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
department_id,
COUNT(*) AS total_employees,
COUNT(*) FILTER (
WHERE
is_active = TRUE
) AS active_employees
FROM
employees
GROUP BY
department_idThe shape
A boolean column is already a condition; FILTER (WHERE is_active = true) just hands that condition straight to the aggregate. Within each department_id partition, the unfiltered COUNT(*) covers every employee and the filtered count covers only the active ones.
Clause by clause
SELECT department_id, COUNT(*) AS total_employees, COUNT(*) FILTER (WHERE is_active = true) AS active_employeesreturns the department, the headcount across every employee, and the active subset of that headcount. TheFILTERrestricts the secondCOUNTto rows whoseis_activeisTRUE; rows whereis_activeisFALSEstay in the partition for the unfiltered total but drop out of the filtered count.FROM employeesreads the employee records.GROUP BY department_idpartitions the rows into per-department groups. The two counts evaluate inside each group, with department1's 17 total and 16 active reflecting a single inactive employee in that department.
Why this and not COUNT(CASE WHEN is_active = true THEN 1 END)
For a boolean column, the CASE WHEN form is workable and produces the same numbers. FILTER is the cleaner expression of the same intent: the condition appears once, adjacent to the function it restricts, instead of buried inside an argument. The parallel between the unfiltered total and the conditional count is also more visible — both are COUNT(*) on the same partition, with only the FILTER distinguishing them.
You practiced COUNT(*) FILTER (WHERE flag = TRUE) — the cleaner shape for 'count records where a boolean column is true' alongside a total count, in a single pass.
