Lesson 8 / 25
Subtotals With ROLLUP and GROUPING SETS
Totals in the same query.
By Rahul Agarwal · 15 years in web development
Multiple grouping levels at once
GROUP BY ROLLUP (a, b) produces groups for (a, b), (a) and the grand total; CUBE adds every combination; GROUPING SETS lists exactly the levels you want. Subtotal rows have NULL in the rolled-up columns, which is ambiguous when the data itself contains NULLs, so use GROUPING(col), which is 1 for a rolled-up column, to label them reliably.
Revenue by city and month with subtotals, run
I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Level 0 rows are detail, level 1 rows are city subtotals, and level 3 is the grand total of 4,650; GROUPING labels them as (all) instead of an ambiguous NULL.
SELECT CASE WHEN GROUPING(c.city) = 1 THEN '(all)' ELSE COALESCE(c.city, '(no city)') END AS city,
CASE WHEN GROUPING(date_trunc('month', o.ordered_on)) = 1 THEN '(all)'
ELSE to_char(date_trunc('month', o.ordered_on), 'YYYY-MM') END AS month,
sum(o.amount) AS revenue,
GROUPING(c.city, date_trunc('month', o.ordered_on)) AS level
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY ROLLUP (c.city, date_trunc('month', o.ordered_on))
ORDER BY GROUPING(c.city), c.city, GROUPING(date_trunc('month', o.ordered_on)), 2;
Output:
city | month | revenue | level -------+---------+---------+------- Delhi | 2026-02 | 450.00 | 0 Delhi | (all) | 450.00 | 1 Pune | 2026-01 | 1500.00 | 0 Pune | 2026-02 | 2700.00 | 0 Pune | (all) | 4200.00 | 1 (all) | (all) | 4650.00 | 3 (6 rows)
Do not confuse NULL data with subtotal NULLs
COALESCE alone would label the grand total as a missing city; GROUPING() distinguishes them.
Quick check: What does GROUPING(col) return for a subtotal row where col is rolled up?
- NULL
- 0
- 1
- The column value
Answer
1 — It identifies aggregated levels.