SQL / WINDOW FUNCTIONS
OVER and the window that leaves rows in place
Use OVER () to attach a whole-result aggregate to every row without collapsing rows, and predict exactly which rows the window contains.
What you will learn
- Attach OVER () to an aggregate to report a whole-result total on every row
- Read empty OVER () as 'the window is every row this query returned'
- Mix bare columns with window aggregates without writing GROUP BY
- Predict window contents: WHERE shrinks it, ORDER BY and LIMIT do not
Understanding OVER and the window that leaves rows in place
SUM(amount) on its own is an instruction to fold many input rows into one output row. Writing SUM(amount) OVER () changes the instruction, not the arithmetic: the same total is computed, but every input row survives and each one receives a copy of the answer in a new column. The number of rows a query returns is decided by FROM, JOIN, WHERE and GROUP BY; a window function never adds or removes a row.
The mental model is per-row: for each row the engine builds a window, which is a set of rows taken from the result the query has produced so far, evaluates the function over that set, and writes the value into that row. Empty parentheses after OVER mean the window is every row, so all rows get the identical value. Because the result is a per-row value rather than a collapsed group, you can put bare columns beside it in the SELECT list, and you can do arithmetic between a row's own column and its window value, which is how share-of-total and difference-from-average columns are written.
Timing explains most surprises. Window functions run after FROM, WHERE, GROUP BY and HAVING, so a row that WHERE removed can never be inside the window and can never contribute to the total. They run before the query's final ORDER BY and LIMIT, so trimming or reordering the output cannot change a value that was already computed. The same ordering rule is why WHERE and HAVING cannot mention a window function or its alias: those clauses have finished by the time the window exists.
WITH sales(region, rep, amount) AS (
SELECT 'North', 'Ada', 400 UNION ALL
SELECT 'North', 'Bo', 100 UNION ALL
SELECT 'South', 'Cy', 250 UNION ALL
SELECT 'South', 'Di', 250 UNION ALL
SELECT 'South', 'Eve', 500
)
SELECT rep,
amount,
SUM(amount) OVER () AS grand_total,
ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM sales
ORDER BY rep;OVER turns an aggregate into a per-row calculation that reads many rows but returns one value for each row, so no rows are collapsed.
Worked examples
WHERE decides what is in the window
Rows removed by WHERE never reach the window, so the same OVER () produces a smaller total.
WITH sales(region, rep, amount) AS (
SELECT 'North', 'Ada', 400 UNION ALL
SELECT 'North', 'Bo', 100 UNION ALL
SELECT 'South', 'Cy', 250 UNION ALL
SELECT 'South', 'Di', 250 UNION ALL
SELECT 'South', 'Eve', 500
)
SELECT rep, amount, SUM(amount) OVER () AS window_total
FROM sales
WHERE region = 'South'
ORDER BY rep;Example explained
Line 1WHERE region = 'South' is evaluated before the window is formed, so only three rows are candidates.
Line 2SUM(amount) OVER () is 1000 here, against 1500 in the unfiltered query, from identical window syntax.
Line 3The two rows with amount 250 stay separate rows; the window value is copied into each, not merged.
LIMIT trims output, not the window
The window value is computed before ORDER BY and LIMIT, so a page of rows can still report the full total.
WITH sales(rep, amount) AS (
SELECT 'Ada', 400 UNION ALL
SELECT 'Bo', 100 UNION ALL
SELECT 'Cy', 250 UNION ALL
SELECT 'Di', 250 UNION ALL
SELECT 'Eve', 500
)
SELECT rep,
amount,
SUM(amount) OVER () AS grand_total,
COUNT(*) OVER () AS rows_matched
FROM sales
ORDER BY amount DESC
LIMIT 2;Example explained
Line 1Each of the five rows gets grand_total 1500 and rows_matched 5 before ORDER BY runs.
Line 2ORDER BY amount DESC then sorts, and LIMIT 2 discards three rows that already carried their values.
Line 3rows_matched still reports 5, which is why COUNT(*) OVER () is the usual way to return a page plus the size of the full result.
Several windows and no GROUP BY
Multiple window aggregates sit beside bare columns in one SELECT list without any grouping clause.
WITH orders(id, qty) AS (
SELECT 1, 3 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 3, 5
)
SELECT id,
qty,
COUNT(*) OVER () AS rows_returned,
MIN(qty) OVER () AS smallest,
MAX(qty) OVER () AS largest
FROM orders
ORDER BY id;Example explained
Line 1Three different aggregates share the same empty OVER () and are each evaluated over the same three rows.
Line 2id and qty appear unaggregated and unlisted in any GROUP BY, which is legal because nothing is being folded.
Line 3COUNT(*) OVER () counts rows in the window rather than groups, so it reports the size of the result set.
Important notes
A window function is legal only in the SELECT list and in ORDER BY, and it cannot be nested inside another aggregate, so SUM(SUM(x) OVER ()) is invalid.
OVER requires SQLite 3.25+, MySQL 8.0+, MariaDB 10.2+, PostgreSQL 8.4+ or SQL Server 2005+; on older engines the same repeated total needs a scalar subquery or a self-join.
Common mistakes
Leaving OVER off, as in SELECT rep, SUM(amount) FROM sales: PostgreSQL and SQL Server reject it because rep is not grouped, while SQLite and MySQL quietly return one row with an arbitrary rep, which looks like a working query.
Writing SUM(amount) OVER (ORDER BY rep) when a grand total was wanted: ordering the window brings a default frame with it, so each row shows a different, growing number instead of 1500.
Referring to the new column in the same query, as in WHERE pct_of_total > 20: this fails with an unknown-column error because WHERE is evaluated before any window function exists.
Try it yourself
Change, predict, then run
In a browser SQL editor, build a five-row inline table of expenses with category and amount columns, then return every row with its own amount, COUNT(*) OVER () as the number of rows, and each amount as a percentage of SUM(amount) OVER (). Confirm the row count matches the plain SELECT and that the percentages add up to about 100 once rounding is allowed for.
Open the SQL workspaceCheck your understanding
A payments table has 12 rows, 7 of which have amount > 100. You run: SELECT id, amount, SUM(amount) OVER () AS total FROM payments WHERE amount > 100 ORDER BY amount DESC LIMIT 3; What does total hold in each of the three returned rows?
- The sum of the 7 rows that passed WHERE, identical in all three rows
- The sum of all 12 rows in the table
- The sum of only the 3 rows that LIMIT kept
- A sum that grows from the first returned row to the third
Show answer
The window is formed after WHERE, so it holds exactly the 7 surviving rows and every one of them gets 1 total. Answer 3 is tempting because those are the rows you can see, but ORDER BY and LIMIT run after the window function has already written its value, so they can only discard rows, never recompute. A growing sum would require an ORDER BY inside the parentheses, and OVER () has none.