How to add subtotals and a grand total
A report often needs totals at several levels: each shop, each product within a shop, and the grand total. GROUP BY ROLLUP produces all of them in one query.
Without it you would run three queries and paste the results together. With it, one pass over the data does the job.
By the end of this lesson
Note
I can total at several levels in one query.
Key words
| Word | What it means |
|---|---|
| Subtotal | a total for one group inside a bigger one |
| Grand total | the total of every row |
| ROLLUP | a grouping that adds subtotals from the last column back |
| CUBE | a grouping that adds totals for every combination |
| FILTER | a clause that makes one aggregate count only some rows |
The cells students fill in are left blank in this preview.
ROLLUP adds subtotals and a grand total
group by rollup (shop, product) gives a row for each shop and product. It also gives a subtotal row for each shop, and one grand total row.
In a subtotal row the column that was rolled up is NULL. In the grand total row both columns are NULL.
Try it: a rollup of shop and product
-- A total for each shop and product, a subtotal for each shop,
-- and a grand total at the end.
select shop, product, sum(amount) as total
from orders
group by rollup (shop, product)
order by shop nulls last, product nulls last;
Worked example: rollup rows for two shops
| shop | product | total | What the row is |
|---|---|---|---|
| Hull | cake | 20 | One shop and product |
| Hull | tea | 15 | One shop and product |
| Hull | NULL | 35 | Subtotal for Hull |
| Leeds | cake | 30 | One shop and product |
| Leeds | NULL | 98 | Subtotal for Leeds |
| NULL | NULL | 235 | Grand total |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think a NULL in a rollup means the data is missing. Here it means "all of these". Use coalesce(shop, 'All shops') to put a word there, or the grouping() function, which gives 1 on a rolled up column.
CUBE totals every combination of the columns
A rollup goes in one direction: shop, then shop and product. It never gives the total for a product across all shops.
cube (shop, product) gives every combination. It adds a row for each product on its own: cake 80, coffee 68 and tea 87 across all three shops.
Try it: a cube of shop and product
-- Every combination: also a total for each product on its own.
select shop, product, sum(amount) as total
from orders
group by cube (shop, product)
order by shop nulls last, product nulls last;
Worked example: how many rows each grouping gives
| Grouping | Rows here | What it adds |
|---|---|---|
| group by shop, product | 8 | Only the detail rows |
| rollup (shop, product) | 12 | Subtotal for each shop, grand total |
| cube (shop, product) | 15 | Also a total for each product |
The cells students fill in are left blank in this preview.
FILTER counts only some rows in one aggregate
sum(amount) filter (where product = 'tea') adds only the tea rows. Write one of these for each product and you have a pivot: a table with one column for each value.
A product that a shop never sold gives NULL, because no row passed the filter. Hull sold no coffee, so its coffee cell is empty.
Try it: a pivot written by hand
-- A pivot written by hand: one column for each product.
select shop,
sum(amount) as total,
sum(amount) filter (where product = 'tea') as tea,
sum(amount) filter (where product = 'coffee') as coffee,
sum(amount) filter (where product = 'cake') as cake
from orders
group by shop
order by shop;
Going further
Change rollup (shop, product) to rollup (product, shop). Say which subtotals you get now, and why the order of the columns matters.
Then use filter to count only the orders of 30 or more in each shop.