Scenario: Helix Systems' compensation committee is benchmarking each department's average current salary, and departments with no currently salaried employees should appear in the report with an average of 0 rather than dropping out.
Task: Write a query to return each department's id, name, and avg_current_salary, with the average reported as 0 for departments with no current salary records.
Assumptions:
- An active salary record has
end_daterecorded as a missing value. - A department's
avg_current_salaryis the average of every active salary across its employees. - The result covers every department.
- A department with no active salary records on file appears with
avg_current_salaryreported as0.
Output:
- One row per department.
- Columns in this order:
department_id,department_name,avg_current_salary. - 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,
COALESCE(AVG(s.amount), 0) AS avg_current_salary
FROM
departments d
LEFT JOIN employees e ON e.department_id = d.id
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 walk from departments down through employees to salaries, and COALESCE(AVG(s.amount), 0) converts the empty-group NULL that AVG produces for a department with no salary records on file. The LEFT JOINs preserve both the empty departments and the salary-less employees inside populated departments.
Clause by clause
SELECT d.id AS department_id, d.name AS department_name, COALESCE(AVG(s.amount), 0) AS avg_current_salaryreturns one row per department with the substituted average attached.FROM departments d LEFT JOIN employees e ON e.department_id = d.idkeeps every department in the result, even those with no employees.LEFT JOIN salaries s ON s.employee_id = e.id AND s.end_date IS NULLextends the chain to active salaries while preserving employees with no active salary record. Theend_date IS NULLtest lives inside theONclause so the salary-less employees survive the join withs.amountset to NULL.GROUP BY d.id, d.namecollapses everything back to one row per department.ORDER BY d.namesorts the report alphabetically.
The trap
Two distinct NULLs travel through this pipeline and only one of them matters. NULL s.amount for a salary-less employee in a populated department is harmless because AVG ignores NULL inputs — the populated salaries in that department still produce a real average. NULL s.amount for every employee in a department with no active salaries on file (or in a department with no employees at all) is what produces the empty-group NULL out of AVG, and that is the NULL COALESCE is sized for. Putting COALESCE(s.amount, 0) inside the aggregate would silently average those zeros into populated departments and lower their benchmark. The substitution belongs around the AVG result, where the only NULL is the one the report cannot use.
You practiced left-joining a parent through two layers down to the metric level, then substituting 0 for the missing-value average that empty branches would otherwise produce.
