Drill 3 of 12 in Window Functions
0

Window Functions · SUM() OVER () · percent of total

3. Share of total spend

Concept: SUM() OVER () · percent of totalshow

An empty window, OVER (), spans the whole result, so SUM(x) OVER () puts the grand total on every row. After a GROUP BY you can even wrap an aggregate: SUM(SUM(x)) OVER () is the total of all the group sums.

Example (different data):

SELECT region, SUM(sales) AS sales,
  ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 1) AS pct
FROM orders
GROUP BY region;

Your task

The product team wants to know which spending categories matter most, as a share of all card spend.

Return category, total (sum of amount) and pct_of_total (that total as a percentage of all spend, rounded to 1 decimal), largest share first.

Starting Postgres in your browser…

Checking your access…