Subtotals With ROLLUP and GROUPING SETS

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.

त्वरित जाँच: What does GROUPING(col) return for a subtotal row where col is rolled up?

• NULL
• 0
• 1
• The column value