Advanced · Lesson 13 of 18

Percent of Total

"What share of the payroll does each person take?" You need each row's value and the grand total at the same time. A window with an empty OVER () puts the total on every row.

SELECT first_name, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (), 1) AS pct_of_payroll
FROM employees
ORDER BY salary DESC;

Add PARTITION BY to get a share within each group:

SELECT first_name, department_id, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department_id), 1) AS pct_of_dept
FROM employees
ORDER BY department_id, salary DESC;

Why 100.0 and not 100? Salaries are whole numbers, and dividing whole numbers drops the decimals, so Alice's 5.7% would come out as 5. Writing 100.0 makes the math keep its decimals.

Check your understanding

Which expression gives each employee's percent of their department's total salary?

Examples run on the employees sample database. Open any of them in the playground to experiment.

Practice it on a real question

All SELECT & filtering questions

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app