Share of Total in SQL

"What share of the category is this product?" needs two numbers on the same row: the product's value and the category's total. GROUP BY gives you the total but loses the products. A window keeps the products and puts the total beside each one, which is the idea from Lesson 11.4 · Window functions, put to work.

select region,
       store,
       sales,
       round(100.0 * sales / sum(sales) over (partition by region), 1) as pct_of_region,
       round(100.0 * sales / sum(sales) over (), 1) as pct_of_company
from store_sales

Group total, whole total

sum(sales) over (partition by region) is the region's total, repeated on every row in the region. sum(sales) over () has no partition, so it is the total of the whole result. Divide the row by either and you have its share. Multiplying by 100.0 first keeps the division decimal in engines such as PostgreSQL, where dividing two integers truncates to 0.

Shares of grouped rows

After a GROUP BY, the window runs over the grouped rows, so it can total an aggregate. sum(sum(x)) over () reads as "the sum of every group's sum":

select c.category_name,
       sum(p.quantity_on_hand * p.cost) as stock_value,
       round(100.0 * sum(p.quantity_on_hand * p.cost)
             / sum(sum(p.quantity_on_hand * p.cost)) over (), 1) as pct_of_shop
from products as p
join categories as c on c.category_id = p.category_id
group by c.category_name
order by stock_value desc

Houseplants hold 36.7% of the shop's stock value, more than the next two categories together.

Instead of a self-join

The same comparisons can be written with a subquery per row or a join back to a grouped copy of the table, as in Lesson 10.4 · Correlated subqueries vs joins. The window version reads the table once and says what it means: price - avg(price) over (partition by category_id) is each product's price against its category average.