OVER: Aggregates Without Collapsing

Since Section 4 you've lived with a trade-off that felt like a law of nature: to compute a total, you GROUP BY — and the detail rows collapse into summaries. You could see the forest or the trees, never both. Window functions repeal that law. One new keyword, OVER, lets any aggregate ride alongside your rows instead of swallowing them — and it unlocks the single most valuable query family in modern analytical SQL.

The problem GROUP BY can't solve

Three café branches, a week of revenue (the daily_sales table below). GROUP BY answers "total per branch" by destroying the dailies:

SELECT branch, sum(revenue) AS total
FROM daily_sales
GROUP BY branch
ORDER BY branch;

 branch |  total
--------+---------
 campus | 3605.75
 center | 6845.75
 harbor | 4950.75
(3 rows)

Twenty-one rows in, three rows out — that's the deal, and for a summary report it's the right deal. But now the boss asks: "show me each day's revenue and what share of the branch's week it was." That sentence needs detail rows and a group total in the same row. With only GROUP BY, you'd query twice and join the summary back onto the detail — clumsy, and by five joins, miserable.

OVER: an aggregate that doesn't collapse

Put OVER () after an aggregate and everything changes:

SELECT day, branch, revenue,
       sum(revenue) OVER () AS grand_total
FROM daily_sales
WHERE day = '2026-03-07'
ORDER BY branch;

    day     | branch | revenue | grand_total
------------+--------+---------+-------------
 2026-03-07 | campus |  610.00 |     2715.25
 2026-03-07 | center | 1215.25 |     2715.25
 2026-03-07 | harbor |  890.00 |     2715.25
(3 rows)

Three rows in, three rows out — every row kept, and each one annotated with the total across the whole result. sum didn't change; the OVER clause changed how it's applied: not "collapse rows and summarize" but "for each row, look out over a set of rows — its window — and summarize them next to it." Empty parentheses mean the window is the entire result.

PARTITION BY: a window per group

The empty OVER () looked at everything. PARTITION BY splits the window into groups — like GROUP BY's targeting system, minus the collapsing. Weekend revenue, annotated with each branch's two-day total:

SELECT day, branch, revenue,
       sum(revenue) OVER (PARTITION BY branch) AS branch_total
FROM daily_sales
WHERE day BETWEEN '2026-03-07' AND '2026-03-08'
ORDER BY branch, day;

    day     | branch | revenue | branch_total
------------+--------+---------+--------------
 2026-03-07 | campus |  610.00 |      1205.00
 2026-03-08 | campus |  595.00 |      1205.00
 2026-03-07 | center | 1215.25 |      2395.25
 2026-03-08 | center | 1180.00 |      2395.25
 2026-03-07 | harbor |  890.00 |      1765.50
 2026-03-08 | harbor |  875.50 |      1765.50
(6 rows)

Each row's window is now "rows sharing my branch" — campus rows see 1205.00, center rows see 2395.25. Six rows stayed six rows.

The payoff: percent-of-total in one pass

Because the annotation lives on every row, arithmetic between a row and its group is now just… arithmetic. The classic that stumps GROUP BY — each branch's share of the day — in one readable query:

SELECT day, branch, revenue,
       round(100 * revenue / sum(revenue) OVER (PARTITION BY day), 1) AS pct_of_day
FROM daily_sales
WHERE day = '2026-03-08'
ORDER BY pct_of_day DESC;

    day     | branch | revenue | pct_of_day
------------+--------+---------+------------
 2026-03-08 | center | 1180.00 |       44.5
 2026-03-08 | harbor |  875.50 |       33.0
 2026-03-08 | campus |  595.00 |       22.4
(3 rows)

Read the window expression like a sentence: this row's revenue, divided by the summed revenue over all rows of the same day. Share-of-group, percent-of-total, contribution analysis — this one pattern is a measurable slice of all analytics SQL ever written.

Where windows are allowed — and why not WHERE

Try the break-it line above and you get a refreshingly direct error:

ERROR:  window functions are not allowed in WHERE
LINE 1: SELECT day FROM daily_sales WHERE revenue > avg(revenue) OVER ()
                                                    ^

The reason is the logical order of a query, which you learned in 4.4: WHERE filters rows early, while windows are computed late — over the rows that survived filtering, just before ORDER BY. A window result can't steer the filter that produced its own input; the snake can't eat its tail. Windows may appear in SELECT and in ORDER BY, nowhere else. When you genuinely want to filter by a window's result — "days above the branch average" — compute the window first and filter one step later, in an outer query or a CTE (7.3's pipelines, meeting 13.4's patterns).

You now hold the core: OVER to annotate, PARTITION BY to scope. Next lesson adds the second dial — ordering inside the window — and with it the ranking functions that answer "top N" questions: ROW_NUMBER, RANK, and DENSE_RANK.