PatternSQL
Percent of total per row
Each row's share of the whole — a product's percent of total revenue — from one row divided by a window SUM(), no self-join required.
Last updated
Pattern
SELECT product, revenue, revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total FROM product_revenue ORDER BY pct_of_total DESC;
Why it’s written this way
SUM(revenue) OVER () with an empty OVER() computes one number — the grand total across every row — and repeats it beside each row instead of collapsing the table the way a plain GROUP BY SUM would. Dividing each row's own revenue by that repeated total produces a per-row percentage without a self-join or a second pass over the data.
Add PARTITION BY category inside the OVER() and the denominator becomes each row's category total instead of the grand total — that one clause is the difference between 'percent of everything' and 'percent of category'.
Edge cases to know
- →The literal is written as 100.0, not 100, specifically to force floating-point division — revenue * 100 / SUM(revenue) with all-integer columns can truncate to whole-number percentages in engines, DuckDB included, that do integer division on two integers.
- →A grand total of zero (an empty table, or all-zero revenue) makes every row divide by zero — decide up front whether that should surface as NULL, as 0, or be filtered out before the query runs.
- →Window functions evaluate after WHERE and GROUP BY but before ORDER BY, so filtering to a subset of products changes what 'the total' means — SUM() OVER() only ever sees the rows that survived the earlier clauses.