OVER and PARTITION BY

The problem GROUP BY cannot solve

In module 4 you used GROUP BY to get one row per department with its average salary. But a manager often wants something else: every employee, with their own salary and the department average next to it, so she can see who is above or below. GROUP BY cannot do that, because it collapses each group into a single row and the individual names are gone.

SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;
-- NULL | 45000
-- 1    | 140000
-- 2    | 80666.67   (rounded here)
-- 3    | 86500
-- 4    | 80000

A window function keeps the rows

A window function works out a value over a set of rows related to the current row, called the window, and writes the answer on the current row. No row disappears. You turn an ordinary aggregate such as SUM, AVG, COUNT, MIN or MAX into a window function by adding the word OVER after it.

SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;
-- Meera Iyer   | 1 | 250000 | 140000
-- Rahul Sharma | 1 | 120000 | 140000
-- Anita Desai  | 1 | 95000  | 140000
-- Vikram Singh | 1 | 95000  | 140000
-- Pooja Gupta  | 2 | 110000 | 80666.666...
-- ... 11 rows in all

OVER () and PARTITION BY

• OVER () with empty brackets: the window is every row of the result. SUM(salary) OVER () puts the total payroll, 1100000, on all 11 rows.
• OVER (PARTITION BY dept_id): the rows are split into partitions, one per department, and each row sees only its own partition. A partition is like a group in GROUP BY, except the rows are not squeezed together.
• You can partition by several columns: PARTITION BY city, course.
• NULL forms its own partition, just as it forms its own group in GROUP BY. Deepak (no department) is alone in his partition, so his "department average" is his own salary.

Comparing a row with its group

Because the group value sits on the same row, you can do arithmetic with it. Two everyday questions are "how far is each person from the department average?" and "what share of the department payroll does each person take?".

SELECT name, dept_id, salary,
       salary - AVG(salary) OVER (PARTITION BY dept_id) AS vs_avg,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept_id), 1) AS pct_of_dept
FROM employees
WHERE dept_id = 1;
-- Meera Iyer   | 1 | 250000 | 110000 | 44.6
-- Rahul Sharma | 1 | 120000 | -20000 | 21.4
-- Anita Desai  | 1 | 95000  | -45000 | 17
-- Vikram Singh | 1 | 95000  | -45000 | 17

When is the window worked out?

Window functions run late: after FROM, WHERE, GROUP BY and HAVING, at the same time as the SELECT list, and before ORDER BY and LIMIT. Two things follow. First, a WHERE changes what the window sees: in the query above, the percentages are shares of Engineering only, because the other rows were already filtered out. Second, you cannot use a window function inside WHERE; topic 5 shows the way around that.

You can even put a window over a GROUP BY result. SUM(SUM(qty * price)) OVER () means "the total of the group totals", which gives each category's share of all revenue in one query.

SELECT p.category, SUM(i.qty * i.price) AS revenue,
       ROUND(100.0 * SUM(i.qty * i.price) / SUM(SUM(i.qty * i.price)) OVER (), 1) AS pct
FROM order_items i JOIN products p ON p.id = i.product_id
GROUP BY p.category
ORDER BY revenue DESC;
-- Fashion     | 7695 | 47.1
-- Electronics | 3998 | 24.5
-- Grocery     | 2750 | 16.8
-- Kitchen     | 1900 | 11.6

Where it works

Window functions are part of standard SQL. They work in PostgreSQL, SQL Server, Oracle, SQLite 3.25 and later, MySQL 8.0 and later, and MariaDB 10.2 and later. Very old MySQL 5.7 servers do not have them.

Common mistakes

• Expecting fewer rows. A window function never removes rows; if you want one row per group, use GROUP BY.
• Writing 100 * salary / SUM(...) and getting whole numbers. Use 100.0 so the division keeps decimals.
• Forgetting that WHERE runs first. If you filter to one department, OVER () totals only that department.
• Mixing a plain column and an aggregate without OVER: SELECT name, AVG(salary) FROM employees returns one row in SQLite and an error in most other databases. Add OVER () or a GROUP BY.
• Putting a window function in WHERE. It gives "misuse of window function". Compute it in a CTE first (topic 5).