Is SUM(SUM(amount)) OVER () legal in a GROUP BY query, and what does it compute?

## The rule behind it Window functions are computed **after** `GROUP BY` and `HAVING`. That single fact answers the whole question. In a grouped query the input to every window function is the set of grouped rows — one row per group — so a window function in such a query is operating on summarised rows, and its arguments must be things a grouped row actually has. ## Reading the double SUM ```sql SELECT region, SUM(amount) AS region_total, SUM(SUM(amount)) OVER () AS grand_total FROM sales GROUP BY region; -- EAST | 300 | 550 -- WEST | 250 | 550 ``` Read it inside-out: 1. `GROUP BY region` produces one row per region. 2. The inner `SUM(amount)` is an ordinary aggregate: it collapses each region's detail rows to a total. After this step the intermediate rows are `(EAST, 300)` and `(WEST, 250)`. 3. `SUM(...) OVER ()` is a window function over those two rows. The empty `OVER ()` means the window is every row of that intermediate set, so it computes `300 + 250 = 550` and returns it on each row. So the two `SUM`s do different jobs at different stages. This is **not** an aggregate nested inside another aggregate, which standard SQL forbids — the outer one is a window function, and the stage boundary between grouping and windowing is exactly what makes it well defined. ## Why it is useful The pattern gives you group-level context inside a grouped report without a second query or a self-join: ```sql SELECT region, SUM(amount) AS region_total, RANK() OVER (ORDER BY SUM(amount) DESC) AS region_rank, SUM(amount) - AVG(SUM(amount)) OVER () AS vs_avg_region FROM sales GROUP BY region; ``` Each region row now knows how it ranks among regions and how far it sits from the average region — all computed over the grouped rows, all in one statement. Without windowing you would aggregate into a CTE and then either join it to itself or aggregate it a second time. ## What you may reference inside OVER The expression inside a window function — and inside its `PARTITION BY` and `ORDER BY` — must be valid at the post-grouping stage. That means: - **Grouping columns**: fine. `PARTITION BY region` in a query grouped by `region, month` is legal and gives windows over the months of a region. - **Aggregates**: fine. `ORDER BY SUM(amount) DESC`, `PARTITION BY region ORDER BY COUNT(*)`. - **Ungrouped detail columns**: not fine. In a query grouped by `region`, `ROW_NUMBER() OVER (ORDER BY rep)` is invalid, because `rep` was collapsed away — a group row has no single `rep` value to order by. Engines report this as the same class of error you get from putting `rep` bare in the SELECT list of a grouped query. ## A worked two-level example ```sql -- Share of the company total, computed per region, in one grouped query SELECT region, SUM(amount) AS region_total, 100.0 * SUM(amount) / SUM(SUM(amount)) OVER () AS pct_of_company FROM sales GROUP BY region; ``` The denominator is the sum of every group's total, so the percentages add up to 100 across the returned rows — with the important caveat that "every group" means every group **this query produced**, i.e. after `WHERE` and `HAVING`. If a `HAVING SUM(amount) > 100` dropped a small region, that region is gone before the window runs and its amount is not in the denominator. ## When to avoid the nesting Some readers find `SUM(SUM(x)) OVER ()` genuinely hard to parse, and not every engine's window support is equally complete. Both concerns have the same escape hatch: aggregate in a CTE, then apply the window to the CTE's rows in the outer query. ```sql WITH by_region AS ( SELECT region, SUM(amount) AS region_total FROM sales GROUP BY region ) SELECT region, region_total, SUM(region_total) OVER () AS grand_total FROM by_region; ``` This is exactly equivalent and spells out the two stages the nested form implies. Choose it when clarity matters more than compactness, or if your engine rejects the nesting. ## What an interviewer is checking That you know windows run after grouping — the double `SUM` is only surprising if you believe otherwise — and that you can state the restriction it implies: inside `OVER`, grouped columns and aggregates only.