Percent of Total in SQL (Window Function Method)

Percent of grand total

SELECT
  product,
  revenue,
  ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total
FROM product_sales;

SUM(revenue) OVER () — with empty parentheses and no PARTITION BY — computes the sum across every row in the result set, but as a window function it returns that same total on every row instead of collapsing them into one, which is exactly what's needed as a shared denominator.

Why this beats a subquery

-- The older way: a scalar subquery recomputes the total separately
SELECT
  product,
  revenue,
  ROUND(revenue * 100.0 / (SELECT SUM(revenue) FROM product_sales), 2) AS pct_of_total
FROM product_sales;

Both queries return the same result, but the window function version computes the total once as part of a single pass over the data, while the subquery version is logically a second, independent scan of the same table to get that total — on a small table the difference is negligible, but it widens as the table grows.

Percent of total within a group

SELECT
  region,
  product,
  revenue,
  ROUND(revenue * 100.0 / SUM(revenue) OVER (PARTITION BY region), 2) AS pct_of_region
FROM product_sales;

Adding PARTITION BY region restarts the sum for each region independently — every product's percentage now reflects its share of its own region's total, not the company-wide total from the earlier example.

Integer division and divide-by-zero

-- Integer columns need an explicit cast, or the division truncates to 0 before multiplying
SELECT revenue * 100.0 / NULLIF(SUM(revenue) OVER (), 0) AS pct_of_total
FROM product_sales;

Multiplying by 100.0 (not the integer 100) forces the whole expression into decimal arithmetic before the division happens — doing it the other way around, dividing first, truncates to 0 on integer types before the percentage is even calculated. NULLIF(..., 0) converts a zero total into NULL, so the query returns NULL instead of erroring when a partition's total happens to be zero.

A practical example: revenue share by product

SELECT
  product,
  SUM(revenue)                                              AS total_revenue,
  ROUND(SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER (), 2) AS pct_of_total
FROM product_sales
GROUP BY product
ORDER BY total_revenue DESC;

Nesting SUM(SUM(revenue)) OVER () is intentional here — the inner SUM(revenue) is the regular GROUP BY aggregate per product, and the outer SUM(...) OVER () is a window function summing those already-aggregated per-product totals into a single grand total, available on every row.

Common mistakes

• Multiplying by the integer 100 instead of 100.0 — with integer inputs, this can produce a truncated 0 before the fractional percentage is preserved.
• Forgetting PARTITION BY when a per-group percentage was actually needed, silently computing percent-of-everything instead.
• Not guarding the denominator with NULLIF when a group's total could legitimately be zero.
• Using a correlated subquery for the total on a large table instead of a window function, adding unnecessary repeated scans.