Scenario: Helix Systems' finance team is reviewing each department's share of the company's total salary expenditure across every salary record on file.
Task: Write a query to return each department_id, its dept_salary (combined salary across all of its records), and its pct_of_total (its share of the company-wide salary expenditure expressed as a percentage).
Assumptions:
- A department's
dept_salaryis the combinedamountacross every salary record belonging to its employees. - A department's
pct_of_totalisdept_salarydivided by the combined salary across every department in the result, multiplied by100. - The result covers only departments with at least one salary record.
Output:
- One row per qualifying department.
- Columns in this order:
department_id,dept_salary,pct_of_total.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
dept_totals AS (
SELECT
e.department_id,
SUM(s.amount) AS dept_salary
FROM
salaries s
JOIN employees e ON e.id = s.employee_id
GROUP BY
e.department_id
)
SELECT
dt.department_id,
dt.dept_salary,
dt.dept_salary * 100.0 / (
SELECT
SUM(dept_salary)
FROM
dept_totals
) AS pct_of_total
FROM
dept_totals dtThe shape
A CTE produces one row of dept_salary per department, and a scalar subquery in the SELECT list reads the company-wide total off the same CTE to compute each department's share. The CTE is referenced twice, which makes the named intermediate worth its space.
Clause by clause
WITH dept_totals AS (SELECT e.department_id, SUM(s.amount) AS dept_salary FROM salaries s JOIN employees e ON e.id = s.employee_id GROUP BY e.department_id)joins salaries to employees and totals by department. One row per department that has any salary record.SELECT dt.department_id, dt.dept_salary, dt.dept_salary * 100.0 / (SELECT SUM(dept_salary) FROM dept_totals) AS pct_of_total FROM dept_totals dtreads the CTE as the outer driver and references it a second time inside the scalar subquery to obtain the company-wide sum. The scalar subquery returns a single number that is the same for every outer row, so it acts as the constant denominator.- The multiplier
100.0(not100) keeps the division in numeric territory so the percentage carries a fractional part instead of being truncated.
Why this and not SUM(dept_salary) OVER ()
SELECT department_id, dept_salary, dept_salary * 100.0 / SUM(dept_salary) OVER () AS pct_of_total FROM dept_totals produces identical results in one pass and is the more compact form. Both are correct. The scalar-subquery shape used here makes the company-wide denominator read as a named operation against the CTE, which can be easier to debug if the percentage logic gets more complicated later.
The trap
The denominator is "the combined salary across every department in the result," not "the company-wide salary across every row in salaries." Those numbers are the same here because every salary belongs to a department that ends up in the CTE, but the wording matters if a future filter on the outer query reduces which departments appear. A common mistake is computing the denominator off the raw salaries table instead of off the CTE, which would silently desynchronize numerator and denominator the moment the outer query's row set changes.
You practiced computing per-department totals once in a CTE and reusing that same CTE in a scalar subquery for the company-wide denominator — a shape only a CTE supports without recomputing.
