# Subtotals With ROLLUP and GROUPING SETS — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/g-rollup

> Totals in the same query.

## 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.

```sql
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.

**Quiz:** What does GROUPING(col) return for a subtotal row where col is rolled up?

- [ ] NULL
- [ ] 0
- [x] 1
- [ ] The column value

*Answer:* 1. It identifies aggregated levels.