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
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, as defensive portability: Postgres and SQLite truncate when both operands are integers, so an all-integer version can silently return whole-number percentages there. DuckDB does not need it — its / is always float division (5/2 is 2.5; // is the integer operator) — but the query is worth writing so it survives the trip to another engine.
- →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.
Part by part
- SUM(revenue) OVER ()
- An empty OVER clause means the whole result set, so this is the grand total repeated on every row. That is exactly what a percent-of-total needs and rarely what you want elsewhere.
- revenue * 100.0
- Multiplying by a decimal literal forces floating-point arithmetic. Without the decimal point, integer division truncates the result to zero.
- ORDER BY pct_of_total DESC
- Sorting by the computed column. Window functions are evaluated before ORDER BY, so referencing the alias here is valid.
Common mistakes
revenue * 100 / SUM(revenue) OVER ()With integer columns this performs integer division, so every percentage below one truncates to zero and the rest lose their decimals. The decimal point in 100.0 is the whole fix.
revenue * 100.0 / SUM(revenue) OVER ()revenue * 100.0 / (SELECT SUM(revenue) FROM product_revenue)Works, and reads the table a second time. The window function computes the total in the same pass, which is both faster and immune to the two queries seeing different data.
SUM(revenue) OVER (ORDER BY revenue)Adding an ORDER BY turns the window into a running total, so each row is divided by a different denominator. The output looks like percentages and is a cumulative distribution instead.
Questions
Why does an empty OVER clause mean everything?
Because with no partition and no ordering the window is the entire result set. It is the one case where writing OVER with nothing inside is deliberate rather than an oversight.
How do I get percent within a group?
Add a partition: SUM(revenue) OVER (PARTITION BY category). Each row is then divided by its own category's total, so the percentages sum to 100 within each group rather than across the table.
Do the percentages add up to exactly 100?
Not always, because of floating-point rounding — a set of values can total 99.99 or 100.01 when displayed to two decimals. If the report must balance, round explicitly and give the largest share the remainder.
What happens if the total is zero?
Division by zero, which errors in most databases and returns NULL in a few. Guard it with NULLIF on the denominator, which converts the zero to NULL and yields NULL rather than an error.