How does the subtotals with rollup query work?

GROUP BY ROLLUP (region, product_category) produces the normal groups, a subtotal for each region, and one grand total row. In the subtotal and total rows the rolled-up columns are null. GROUPING(col) returns 1 when the null in that column is a rollup marker, which tells it apart from a real null in the data.

What commonly goes wrong with subtotals with rollup in SQL?

A real null category looks like a subtotal unless you check GROUPING; label them differently.

Subtotals with ROLLUP in SQL

ROLLUP adds subtotal and grand total rows to a grouped query, so one result shows detail, subtotals per group and the overall total.

Example query

This example assumes: sales(id, region text, product_category text, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT region,
       product_category,
       SUM(total_amount) AS sales,
       GROUPING(region) AS is_grand_total,
       GROUPING(product_category) AS is_subtotal
FROM sales
GROUP BY ROLLUP (region, product_category)
ORDER BY region NULLS LAST, product_category NULLS LAST;

How it works

GROUP BY ROLLUP (region, product_category) produces the normal groups, a subtotal for each region, and one grand total row.
In the subtotal and total rows the rolled-up columns are null.
GROUPING(col) returns 1 when the null in that column is a rollup marker, which tells it apart from a real null in the data.

MySQL, BigQuery, SQL Server and SQLite differences

MySQL writes it as GROUP BY region, product_category WITH ROLLUP. SQL Server, BigQuery and PostgreSQL use ROLLUP (...). SQLite has no ROLLUP; combine grouped queries with UNION ALL.
PostgreSQL also supports CUBE and GROUPING SETS for other combinations of subtotals.

Common mistakes

A real null category looks like a subtotal unless you check GROUPING; label them differently.
Adding the subtotal rows to the detail rows double counts; use them for display only.
Sorting nulls last is needed to put totals at the end of each group.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show sales by region and category with subtotals for each region and a grand total.
Give me monthly revenue with a total row at the bottom.