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/chapter_summary/browser_support: on Hindi pages the
view already swaps in the hi_ translation fields (or blanks these
out if untranslated), so this renders correctly for both languages
without a lang_code check here. #}
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: