What does an empty OVER () with no PARTITION BY or ORDER BY compute?

## The empty window `OVER ()` is a complete, legal window definition. Both of its optional parts — `PARTITION BY` and `ORDER BY` — are simply absent, and so is a frame. What is left is the simplest possible window: **one partition containing every row, in no particular order, all of it visible to the function**. ```sql SELECT order_id, amount, SUM(amount) OVER () AS grand_total FROM orders; ``` If `orders` has three rows with amounts 10, 20 and 30, this returns three rows, and every one of them carries `grand_total = 60`. Note carefully what did *not* happen: no rows were removed, and no rows were merged. A window function is row-preserving by definition, and an empty `OVER` clause does not change that. ## "One partition" and "unordered" Omitting `PARTITION BY` is the same as saying there is exactly one group. Omitting `ORDER BY` says the rows in that group have no sequence you may rely on, which in turn means positional concepts do not apply. That has a practical consequence: `OVER ()` is a sensible window for order-insensitive functions — `SUM`, `COUNT`, `AVG`, `MIN`, `MAX` — and a poor one for order-dependent functions. `ROW_NUMBER() OVER ()` is accepted by some engines but has no defined basis for deciding which row is first, so the numbering it produces is arbitrary and may differ between runs. If the numbering matters, give the window an `ORDER BY`. ## What the window covers With no `ORDER BY` and no frame, the window for every row is the entire partition. Because the partition is the entire result set, every row's window is the same set of rows, so the computed value is identical on every output row. That constancy is exactly the point: it lets you carry a whole-result-set aggregate alongside the detail rows. ## Adding ORDER BY changes the answer This is the contrast interviewers are usually probing for: ```sql SELECT order_date, amount, SUM(amount) OVER () AS total_all_rows, SUM(amount) OVER (ORDER BY order_date) AS accumulated FROM orders; ``` The first column is constant. The second grows down the result, because once the rows have a sequence the default window is no longer the whole partition but everything from the start of the partition up to the current row and its ties. Same function, same table, different window definition, completely different column. If someone hands you `SUM(x) OVER (ORDER BY d)` and expects a constant, that expectation is wrong. ## The rows it sees A window operates on the rows the query has already produced at that stage — after `FROM`, after `WHERE`, after `GROUP BY` and `HAVING`. So in: ```sql SELECT customer_id, amount, SUM(amount) OVER () AS filtered_total FROM orders WHERE order_date >= DATE '2024-01-01'; ``` `filtered_total` is the 2024 total, not the all-time total. This is almost always the desired behaviour — the aggregate matches the rows the user is looking at — but it surprises people who read `OVER ()` as "over the table". It means over the *result set*. ## Where it earns its place Three everyday uses: - **Detail plus grand total in one pass.** You get the rows and the total together, without a second query or a self-join to a total subquery. - **Share of total.** `amount * 1.0 / SUM(amount) OVER ()` gives each row's fraction of the whole result. (Watch the division: integer division and a zero denominator are the usual traps.) - **Total row count next to a page of rows.** `COUNT(*) OVER ()` returns the number of rows in the result set on every row, which is a common way to get a count for a paging UI alongside the page itself. Be aware that the count is computed over the rows that reached the window stage; if the query also applies a row limit, whether that limit has been applied yet depends on where the limit sits relative to the window in the query, so read the query carefully rather than assuming. ## Distinguishing it from the alternatives A correlated scalar subquery `(SELECT SUM(amount) FROM orders)` can produce the same constant column, but it is a separate computation over a separately specified set of rows — it does not automatically respect the outer query's `WHERE`, and you must repeat the filter to keep the two in step. `OVER ()` is defined over the result set you already have, so the two cannot drift apart. ## Gotchas worth stating aloud Empty `OVER ()` never reduces the row count; it is not shorthand for `GROUP BY` with no key; it computes over filtered rows, not the base table; and it is not the same as `OVER (ORDER BY ...)`, which introduces a sequence and, with it, a different default window.