GROUP BY with ROLLUP
WITH ROLLUP extends grouped results with subtotal and grand total rows, which is useful for financial and operational reports.
Report Structure
| Level | Example columns | Row meaning |
|---|---|---|
| Detail group | region, product_line | Metric per leaf group |
| Subtotal | region, NULL | Metric per region |
| Grand total | NULL, NULL | Metric across all rows |
Example Query
SELECT region,
product_line,
SUM(total_amount) AS revenue
FROM sales
GROUP BY region, product_line WITH ROLLUP;
Display-Friendly Labels
SELECT COALESCE(region, 'ALL_REGIONS') AS region,
COALESCE(product_line, 'SUBTOTAL_OR_TOTAL') AS product_line,
SUM(total_amount) AS revenue
FROM sales
GROUP BY region, product_line WITH ROLLUP;
| region | product_line | revenue |
|---|---|---|
| EU | Hardware | 45000.00 |
| EU | Software | 61000.00 |
| EU | SUBTOTAL_OR_TOTAL | 106000.00 |
| ALL_REGIONS | SUBTOTAL_OR_TOTAL | 312500.00 |
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Treating subtotal rows as regular groups | Wrong downstream calculations | Detect NULL rollup keys and label them explicitly |
| Applying filters in client only | Inconsistent reporting output | Keep business filters in SQL query |
| Sorting without handling rollup rows | Totals appear in unexpected order | Use explicit ORDER BY strategy for report rendering |
Quick Reference
SELECT a, SUM(v) FROM t GROUP BY a WITH ROLLUP;
SELECT a, b, COUNT(*) FROM t GROUP BY a, b WITH ROLLUP;
SELECT COALESCE(a, 'TOTAL') AS a, SUM(v) FROM t GROUP BY a WITH ROLLUP;