Subtotals with ROLLUP

Per-group rows plus a grand-total row in one query.

Subtotals and grand totals

Reports often want per-category numbers and an overall total. ROLLUP adds the total row automatically — it produces the normal groups plus a summary row where the grouped column is NULL:

SELECT genre, count(*) AS n
FROM books
GROUP BY ROLLUP (genre)
ORDER BY genre NULLS LAST;

The final row (genre = NULL) is the grand total. GROUPING SETS and CUBE generalize this to multiple grouping combinations at once.

ROLLUP adds the summary row for free. The NULL in the grouped column marks "all genres" — it is a total, not a missing value, which is why the query sorts it last.