How do you compute each row's percent of its group total with SUM() OVER (PARTITION BY ...)?

## The shape of a percent-of-total A percent-of-total column answers "how much of its group does this row account for?". It needs two numbers on the same row: the row's own measure, and the total of the group the row belongs to. A plain `GROUP BY` gives you the second number but destroys the first — you get one row per category, not one row per product. A windowed aggregate gives you both, because `SUM(sales) OVER (PARTITION BY category)` computes the category total and attaches it to *every* row of that category without collapsing anything. ```sql SELECT category, product, sales, 100.0 * sales / SUM(sales) OVER (PARTITION BY category) AS pct_of_category FROM product_sales; ``` With rows `('A','p1',10)`, `('A','p2',30)`, `('B','p3',40)`, the first row reports 25.0, the second 75.0, and the third 100.0 — each row's share of *its own* category. ## Choosing the denominator The partition clause is the whole choice of denominator: - `SUM(sales) OVER ()` — total of all rows: percent of the overall result. - `SUM(sales) OVER (PARTITION BY category)` — percent within the category. - `SUM(sales) OVER (PARTITION BY region, category)` — percent within the region-and-category cell. All three can appear in the same `SELECT`, so one query can report a product's share of its category *and* its share of the company at once. Note that you should **not** add an `ORDER BY` inside these windows: an `ORDER BY` turns the denominator into a running total, and dividing by a cumulative sum gives a rising ratio, not a share. ## Decimal arithmetic `sales / SUM(sales) OVER (PARTITION BY category)` where both operands are integers is exposed to integer division: in engines that truncate, every ratio below 1 becomes 0 and the column reads all zeros. Writing the multiplier as `100.0`, or casting one operand (`CAST(sales AS DECIMAL(18,4))`), forces exact-numeric arithmetic and removes the doubt entirely. Because engines differ on how they type integer division, the portable habit is to make the decimal explicit rather than to rely on the engine to do something sensible. ## Zero and empty denominators If every row in a partition has `sales = 0`, the denominator is 0 and the division raises an error in a standard-conforming engine. `NULLIF(SUM(sales) OVER (PARTITION BY category), 0)` converts the 0 denominator to NULL, and the whole expression evaluates to NULL — an honest "undefined share" rather than a failed query. If the report must show a number, wrap that in `COALESCE(..., 0)`. ```sql SELECT category, product, sales, ROUND(100.0 * sales / NULLIF(SUM(sales) OVER (PARTITION BY category), 0), 2) AS pct_of_category, ROUND(100.0 * sales / NULLIF(SUM(sales) OVER (), 0), 2) AS pct_of_all FROM product_sales; ``` ## Negative values change the meaning Percent-of-total is only meaningful when the measure is non-negative. If a category mixes sales and refunds, the totals partly cancel, and a single row can exceed 100% of its category or flip sign. Either restrict the measure to positive rows, or report the share of gross rather than net — the SQL will happily compute a nonsense percentage otherwise. ## Sanity checks an interviewer likes to hear Within one partition, the percentages must add up to 100 (allowing for rounding). If they add to something else, the usual causes are a denominator with an accidental `ORDER BY`, a partition key that does not match the grouping you intended, or duplicated detail rows coming from a join earlier in the query — the total inflates and every share shrinks proportionally. ## Rounding `ROUND(x, 2)` makes the output readable but means the column no longer adds to exactly 100. For displays that must add up, round at presentation time or accept the residual; do not chase it inside SQL. ## Common mistakes - Adding `ORDER BY` to the denominator window and getting a cumulative share. - Integer division silently producing 0 for every row. - No `NULLIF` guard, so a zero-total group aborts the whole report. - Partitioning by the wrong key — for example by product, which makes every row 100%. - Reaching for a self-join or a correlated subquery to fetch the group total when a single `OVER` clause does it.