What does SUM(amount) OVER (ORDER BY order_date) return on each row of a result?

## Two shapes of the same aggregate SUM, COUNT, AVG, MIN and MAX can be written in two different shapes. In the grouped shape — `SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id` — many input rows fold into one output row per group. In the windowed shape you append an `OVER` clause — `SUM(amount) OVER (...)` — and nothing folds: every input row survives, and each row gets its own value of the aggregate, computed over the set of rows the `OVER` clause selects for that row. That set is the row's *window frame*. ## What an empty OVER () computes `SUM(amount) OVER ()` is an empty window specification: no partitioning, no ordering, no explicit frame. The window for every row is the whole set of rows the window step sees, so every row shows the same number — the grand total — beside its own detail columns. `SUM(amount) OVER (PARTITION BY customer_id)` narrows that to "all rows of the same customer", so each row carries its customer's total. ## What ORDER BY inside OVER changes The moment you write an `ORDER BY` inside `OVER`, the window stops being "all rows of the partition" and becomes "the rows of the partition up to and including this one". An `OVER` clause that has an `ORDER BY` but no explicit frame gets the default frame `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. So `SUM(amount) OVER (ORDER BY order_date)` returns, on each row, the sum of `amount` over every row from the earliest `order_date` through that row: a running, or cumulative, total. This is the point to remember: **`ORDER BY` inside `OVER` is not output sorting and not decoration — it changes what the function computes.** The query's own trailing `ORDER BY` decides the order rows are printed in; the window's `ORDER BY` decides the order in which the aggregate accumulates. The two are independent, and a query can perfectly well accumulate by date and print by customer. ## Worked example Given `orders(order_date, amount)` holding `(2024-01-01, 100)`, `(2024-01-02, 50)`, `(2024-01-03, 25)`: ```sql SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total, SUM(amount) OVER () AS grand_total FROM orders; ``` The `running_total` column reads 100, 150, 175 and the `grand_total` column reads 175, 175, 175. Three rows in, three rows out. ## Every aggregate accumulates the same way The `ORDER BY` trick is not special to SUM. `COUNT(*) OVER (ORDER BY order_date)` gives a running count — how many rows have been seen so far. `AVG(amount) OVER (ORDER BY order_date)` gives a running average. `MAX(amount) OVER (ORDER BY order_date)` gives a running maximum, the classic "high-water mark" column, and `MIN` the running low. ## Restarting the accumulation per group Combine both clauses to get a per-group running total: ```sql SUM(amount) OVER (PARTITION BY customer_id ORDER BY txn_date) ``` `PARTITION BY` cuts the rows into independent groups; `ORDER BY` accumulates inside each one. The running total resets to that customer's first amount at every partition boundary. Leaving `PARTITION BY` out is the most common bug in a running-balance query: one customer's balance silently continues from another's. ## Ties in the ordering key Under the default `RANGE` frame, rows that tie on the `ORDER BY` expression are *peers*: every tied row gets the same value — the cumulative total through the last of the tied rows — rather than a step per row. If two orders share a date, both show the same running total. When you want strictly row-by-row accumulation, spell the frame `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` and add a unique tiebreaker (an id) to the window `ORDER BY` so the sequence is deterministic and reproducible between runs. ## Why authors reach for this Before window functions, a running total had to be written as a correlated subquery (`SELECT SUM(amount) FROM orders o2 WHERE o2.order_date <= o1.order_date`) or as a self-join with an inequality predicate. Those are hard to read, hard to extend to per-group resets, and easy to get subtly wrong when the ordering key repeats. One `OVER` clause expresses the same intent in a single expression, and several such expressions can sit side by side in one `SELECT`. ## Common mistakes - Expecting `SUM(x) OVER (ORDER BY d)` to return the grand total, as `OVER ()` would. - Assuming the windowed aggregate collapses rows the way `GROUP BY` does. - Reading the window's `ORDER BY` as sorting for display. - Omitting `PARTITION BY`, so accumulation runs across accounts, regions or customers. - Ordering on a non-unique column and then being surprised that tied rows share a value.