Window aggregates over the entire result
Share of the whole
Lesson 7 of 20 · 10 min · Module 2: Aggregation that answers real questions
"Kitchen made 16 000 cents" means more as "Kitchen made 39% of revenue". Showing each group next to the total requires the total on every row, which window functions provide without a second query.
Start this lesson →Free. No installation: you write and run the code in your browser, and it is checked automatically.
By the end, you’ll be able to…
- Use an empty OVER () for grand totals
- Calculate percentages without integer division
- Layer a CTE and a window function
The idea
SUM(revenue) OVER () is the sum over all rows of the result, repeated on each row. Divide by it to get a share. Multiply by 100.0, not 100, so SQLite uses decimal division, and round with ROUND(x, 1).
SELECT name, stock, SUM(stock) OVER () AS all_stock
FROM products
LIMIT 3;
01OVER () means "all rows"
A window with no PARTITION BY and no ORDER BY covers the whole result. Unlike GROUP BY, it keeps every row and adds the total next to it.
02Integer division truncates
In SQLite, 1 / 3 is 0 because both values are integers. Writing 100.0 makes one side decimal, so the result keeps its fraction.
SELECT 1 / 3, 1.0 / 3, ROUND(100.0 / 3, 1);0 | 0.333… | 33.303Aggregate, then window
Window functions run after GROUP BY. Computing revenue per category in a CTE first keeps each step simple and readable.
Your turn
category, revenue_cents, and percent: the category’s share of all revenue, rounded to one decimal. Sort by revenue, highest first.What the checks look for
- Shows each category’s share of revenue
Take this with you
Window functions put the whole next to each part.
Go a little further
Add a running percentage that shows how much of the revenue the top categories reach together.