ROLLUP
ROLLUP adds subtotal and grand total rows to a grouped result.
In this page:
Syntax
SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY ROLLUP (column1, column2);ROLLUP
GROUP BY ROLLUP (a, b) produces groups for (a, b), then subtotals for (a) and a grand total for ().
Subtotal rows show NULL in the rolled-up columns. The GROUPING() function tells real NULLs from subtotal NULLs.
It is PostgreSQL syntax for reports; the site editor's SQLite does not support it.
Note:
Use GROUPING(col) to label the subtotal rows.
Example: ROLLUP
shop=# SELECT region, item, SUM(qty) AS total
shop-# FROM sales GROUP BY ROLLUP (region, item) ORDER BY region, item;
region | item | total
--------+------+-------
N | book | 1
N | lamp | 2
N | pen | 5
N | | 8
S | book | 3
S | pen | 10
S | | 13
| | 21⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Confusing subtotal NULLs with data NULLs
- Expecting it in SQLite
- Forgetting ORDER BY
Chapter Summary
- ROLLUP adds subtotals and a grand total
- Subtotal rows contain NULL
- GROUPING() identifies them
- Great for report totals
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: