Percent of total with a window function
Show each row's share of the grand total or of its group without a self-join.
- SQL
- GreenSnippets
- MIT
SELECT
region,
product,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
ORDER BY region, revenue DESC;Example / expected output
-- region product revenue pct_of_total pct_of_region
-- EU A 600 30.0 60.0How it works
SUM(...) OVER () computes the total across all rows while still returning every row. Add PARTITION BY for a share within a group. Cast to numeric before dividing so integer division does not round everything to zero.
This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.
Related snippets
- SQL
Moving average with a window frame
Compute a 7-day rolling average with AVG over a ROWS BETWEEN frame.
- SQL
Fill in missing dates with generate_series
Produce a row for every day in a range and left join your data so days with no activity show zero.
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
RANK, DENSE_RANK and ROW_NUMBER
Understand how the three ranking window functions treat ties, with one query that shows all three.