Helix Systems' HR team needs a headcount breakdown for each department: how many employees are currently active and how many are inactive.
Write a query to return the department ID, the active count, and the inactive count.
Assumptions:
- The
employeestable contains every active and former employee. - Active employees have
is_active = TRUE; inactive employees haveis_active = FALSE. - Both counts are produced from the same per-department group.
Output:
- One row per department, with columns
department_id,active_count, andinactive_count.
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(
CASE
WHEN is_active = TRUE THEN 1
END
) AS active_count,
COUNT(
CASE
WHEN is_active = FALSE THEN 1
END
) AS inactive_count
FROM
employees
GROUP BY
department_idThe shape
The boolean column is_active carries the active/inactive distinction directly — no string comparison needed. Two COUNT(CASE WHEN ... THEN 1 END) expressions split each department's employees into active and inactive headcounts in one per-department pass. Department 1 has 16 active and 1 inactive; department 5 has 4 active and 0 inactive.
Clause by clause
department_idis the grouping column, present in both theSELECTlist and theGROUP BYclause per the N014 rule.COUNT(CASE WHEN is_active = TRUE THEN 1 END) AS active_countreturns1forTRUErows andNULLforFALSErows.COUNTtallies the non-NULLvalues inside each department's group.COUNT(CASE WHEN is_active = FALSE THEN 1 END) AS inactive_countis the mirror image. The same row that contributed1to the active count contributesNULLhere.FROM employees GROUP BY department_idpartitions the employees per department; the two aggregates evaluate independently inside each partition.
Why this and not is_active alone in the predicate
PostgreSQL accepts a boolean expression directly inside CASE WHEN, so WHEN is_active THEN 1 would work and return the same result. Writing is_active = TRUE is the more explicit form, and it parallels the is_active = FALSE branch one line down. When the two predicates sit side by side, the symmetry makes the intent easier to read at a glance.
You practiced conditional counting against a boolean column. The shape works the same as against a string column — only the predicate inside CASE changes; the surrounding COUNT(CASE WHEN ... THEN 1 END) scaffold is identical.
