I want every employee AND their department average
HR is putting together a compensation review. They want one report: every employee’s salary, sitting right next to the average salary for their department — so a manager can glance at a row and immediately tell whether that person is paid above or below their department’s norm.
One row per employee. Not one row per department.
I want every employee AND their department average, in the same row.
Okay. What have you got so far?
GROUP BY department, AVG(salary) — wait, no. That collapses everyone into one row per department. I lose the individual employees.
Right, GROUP BY answers 'what's the average per department', full stop. You're asking a slightly different question — 'what's this employee's salary, and also, what's their department averaging'. Both, on the same row.
Could I do it with a subquery, then? For each employee, look up the average salary of everyone in their department.
Now you're thinking. Let's write that one first, actually — it works, and it'll make the better tool obvious.
A correlated subquery — one that refers back to the outer row — can compute “the average salary of everyone in this employee’s department” fresh, for every row:
SELECT
name,
department,
salary,
(SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department) AS dept_avg
FROM employees e
WHERE department = 'Engineering'
ORDER BY salary DESC;| name | department | salary | dept_avg |
|---|---|---|---|
| Meera Rao | Engineering | 165000 | 132600 |
| Sam Okafor | Engineering | 128000 | 132600 |
| Priya Nair | Engineering | 128000 | 132600 |
| Tom Becker | Engineering | 121000 | 132600 |
| Ken Watanabe | Engineering | 121000 | 132600 |
It works. Every Engineering row shows the same 132600 — the department average — sitting right next to that person’s own salary.
But look closer at what SQL is actually doing: for every single row, it re-runs that inner query and recomputes the same average from scratch. Five Engineering rows, five identical recalculations of the exact same number. And the moment HR asks for the department’s max salary too, or its headcount, you’re bolting on another subquery. And another.
This exact shape — keep every row, but also calculate something across a related group of rows — is common enough that SQL has a purpose-built tool for it: window functions.
A window function looks a lot like the aggregates you already know — AVG, SUM, MAX — except you attach OVER (...) to it. That OVER is the signal: “don’t collapse the rows. Calculate this across a window of related rows, and keep every one of them.”
Calculate across related rows, without collapsing them.
Start with the plainest possible window: OVER (), empty parentheses. That means “treat the whole result set as one window” — so this computes the company-wide average salary, but keeps every row:
SELECT name, department, salary,
AVG(salary) OVER () AS company_avg
FROM employees;| name | department | salary | company_avg |
|---|---|---|---|
| Meera Rao | Engineering | 165000 | 112812.5 |
| Lena Fischer | Sales | 150000 | 112812.5 |
| Omar Haddad | Support | 110000 | 112812.5 |
| Ravi Desai | Marketing | 82000 | 112812.5 |
Every row gets the same number — NOVA’s average salary across all 16 employees, 112812.5 — because right now there’s only one window: the whole table. That’s not quite what HR wants, though. They want the average per department, not company-wide. That’s where PARTITION BY comes in: it splits the one big window into smaller windows, one per group.
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;| name | department | salary | dept_avg |
|---|---|---|---|
| Meera Rao | Engineering | 165000 | 132600 |
| Priya Nair | Engineering | 128000 | 132600 |
| Sam Okafor | Engineering | 128000 | 132600 |
| Ken Watanabe | Engineering | 121000 | 132600 |
| Tom Becker | Engineering | 121000 | 132600 |
| Ana Costa | Marketing | 115000 | 98500 |
| Ravi Desai | Marketing | 82000 | 98500 |
Same idea as the subquery, but SQL now does the grouping internally instead of you re-running a query per row. One pass, five department windows, sixteen rows, all kept.
Running…
All sixteen rows, all five departments. Sales and Product land on averages that don’t divide evenly — 114333.33, 116666.67 — which is exactly why the query wraps the window function in ROUND(..., 2). That has nothing to do with windows specifically; it’s the same rounding you’d do around any AVG.
Try it: What if you PARTITION BY manager_id instead of department?
Run your query to see results here.
Look at the rows where manager_id is NULL — that’s Meera, Lena, Omar, Ana, and Ines, the five people who don’t report to anyone in this table. You might expect NULL to break partitioning somehow, since NULL usually means “unknown” and refuses to equal anything, even another NULL. But PARTITION BY treats all the NULLs as one shared group anyway — so all five department heads land in the same window together, averaging 136000 among themselves. Grouping is one of the few places NULL gets to match NULL.
Now the VP wants a different column on that same report: for every employee, show their salary next to the highest salary paid in their department — so it’s obvious at a glance who’s already at the top of their department’s pay range. Return name, department, salary, and dept_max.
Hint 1
You need one row per employee, plus a number describing the whole department that row belongs to — that's the same shape as the AVG example above.
Hint 2
MAX() works as a window function exactly like AVG() does — same OVER (PARTITION BY ...), just a different aggregate.
Hint 3
SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) AS dept_max FROM employees;
Solution
Running…
The subquery version and the window function version compute the exact same numbers. The difference is how SQL gets there: GROUP BY collapses rows down to one per group; a correlated subquery keeps every row but recalculates its answer independently for each one; OVER (PARTITION BY ...) keeps every row and computes the group-level answer once, broadcasting it back to every row in that group.
GROUP BY collapses rows into one per group. Window functions — OVER (PARTITION BY ...) — let you calculate across related rows without collapsing them at all.