SQL › Subtotals and Grand Totals

ROLLUP, CUBE and GROUPING SETS

A normal GROUP BY gives one level of groups. ROLLUP, CUBE and GROUPING SETS add more levels to the same result, such as subtotals and a grand total.

Syntax

Order of execution: FROM → WHERE → GROUP BY (every grouping level) → HAVING → SELECT → ORDER BY

Code
SELECT a, b, AGG(column)
FROM table_name
GROUP BY ROLLUP (a, b);          -- or CUBE (a, b), or GROUPING SETS ((a, b), (a), ())

On a total row, the columns being added up show NULL.

PostgreSQL, SQL Server and Oracle have all three. MySQL has only ROLLUP, written GROUP BY a, b WITH ROLLUP.

Examples

A grand total with ROLLUP

The extra row, with NULL as the method, is the total of all methods.

Code
SELECT method, SUM(amount) AS total
FROM payments
GROUP BY ROLLUP (method)
ORDER BY method;

Subtotals with ROLLUP

With two columns, ROLLUP adds a subtotal for each value of the first, then a grand total.

Code
SELECT category_id, is_available, COUNT(*) AS products
FROM products
GROUP BY ROLLUP (category_id, is_available)
ORDER BY category_id, is_available;

Every combination with CUBE

CUBE also totals the second column on its own: each status across all years.

Code
SELECT EXTRACT(YEAR FROM order_date) AS year, status, COUNT(*) AS orders
FROM orders
GROUP BY CUBE (EXTRACT(YEAR FROM order_date), status)
ORDER BY year, status;

Exactly the groups you list

GROUPING SETS makes only the groups named, plus () for a grand total.

Code
SELECT category_id, is_available, COUNT(*) AS products
FROM products
GROUP BY GROUPING SETS ((category_id), (is_available), ())
ORDER BY category_id, is_available;

Labelling the total rows

GROUPING() tells a total row apart from a real NULL, and it can sort totals last.

Code
SELECT CASE WHEN GROUPING(method) = 1 THEN 'all methods' ELSE method::text END AS method,
       COUNT(*) AS payments
FROM payments
GROUP BY ROLLUP (method)
ORDER BY GROUPING(method), payments.method;   -- the column, not the text alias
 
-- Fails: COALESCE keeps the column's type, and 'all methods' isn't a payment method
SELECT COALESCE(method, 'all methods') FROM payments GROUP BY ROLLUP (method);

Remember

• ROLLUP adds totals from right to left; CUBE adds every combination.
• A NULL in a total row means "all values"; use GROUPING() when the column can also hold real NULLs.
• Cast enums and numbers to text before putting a label like 'TOTAL' in the same column.
• ORDER BY GROUPING(column) puts the total rows after the others.

Practice

1. Revenue by Status With a Total (Medium)
2. The Payments Grid (Medium)
3. Orders by City and by Status (Medium)