Scenario: Helix Systems' finance team needs every department paired with the count of employees who are both currently active and have an active salary on record — departments with no qualifying employees should appear with a count of 0.
Task: Write a query to return each department's id, name, and active_salaried_count — the count of employees in that department who are both active and have an active salary on record, reported as 0 for departments with none.
Assumptions:
- An active employee has
is_activeequal toTRUE. - An active salary record has
end_daterecorded as a missing value. - A department's
active_salaried_countis the count of employees who are both active and have an active salary record on file. - The result covers every department.
- A department with no qualifying employees appears with
active_salaried_countof0.
Output:
- One row per department.
- Columns in this order:
department_id,department_name,active_salaried_count. - Sorted by
department_nameascending.
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
d.id AS department_id,
d.name AS department_name,
COUNT(s.employee_id) AS active_salaried_count
FROM
departments d
LEFT JOIN employees e ON e.department_id = d.id
AND e.is_active = TRUE
LEFT JOIN salaries s ON s.employee_id = e.id
AND s.end_date IS NULL
GROUP BY
d.id,
d.name
ORDER BY
d.nameThe shape
Two chained LEFT JOINs, each carrying its qualification inside its own ON clause, let every department reach the count step regardless of whether any employee survives both filters. COUNT(s.employee_id) counts only the salaries that actually matched at the deepest level — so a department whose employees are inactive, or whose active employees have no active salary, lands on 0 instead of being dropped.
Clause by clause
SELECT d.id AS department_id, d.name AS department_name, COUNT(s.employee_id) AS active_salaried_countreturns one row per department with the count of fully-qualifying employees attached.FROM departments d LEFT JOIN employees e ON e.department_id = d.id AND e.is_active = TRUEkeeps every department in the result. Inactive employees fail the join condition, so they are treated as no-match for the department; a department with only inactive employees survives withe.idset to NULL.LEFT JOIN salaries s ON s.employee_id = e.id AND s.end_date IS NULLextends the chain. An employee row already populated by the first join gets paired with their active salary; an employee with no active salary fails this join's condition and arrives at the count withs.employee_idas NULL.GROUP BY d.id, d.namecollapses back to one row per department.ORDER BY d.namesorts alphabetically.
The trap
Counting the wrong column collapses the report. COUNT(e.id) here would over-report — it counts every active employee, including those with no active salary record. COUNT(*) would over-report further — it counts the placeholder row even for a department with no employees at all, putting 1 where the report needs 0. COUNT(s.employee_id) is the only choice that survives the chain correctly: it counts non-NULL values from the deepest join, so it counts exactly the employees who passed both the activity filter and the active-salary filter. The position of each filter inside its own ON clause is what carries the empty cases through to the count; the choice of column inside COUNT is what makes the count actually report zero.
You practiced chaining two left-joins, each carrying its own qualification inside the join condition, so departments with no qualifying employees still arrive at the count step and report 0.
