{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"rollups-and-subtotals","title":"Subtotals and grand totals in one query: ROLLUP and GROUPING","url":"https://addaly.com/learn/data-and-sql/rollups-and-subtotals","position":31,"of":66,"minutes":8,"answer":"ROLLUP stacks several grains in one result, so use GROUPING() to tell a subtotal's NULL from a real one and never sum across the output, which counts each row once per level.","keyIdea":"ROLLUP stacks several grains in one result, so use GROUPING() to tell a subtotal's NULL from a real one and never sum across the output, which counts each row once per level."},"body":{"format":"markdown","text":"## Three grains in one report\n\nA sales report wants revenue by city, a subtotal for each region, and a grand total at the bottom. That is three queries with three different GROUP BY clauses, and three scans of the table. Or it is one:\n\n```sql\nSELECT region, city, sum(amount) AS revenue\nFROM sales\nGROUP BY ROLLUP (region, city)\nORDER BY region, city;\n```\n\n`ROLLUP (region, city)` produces the piles for `(region, city)`, then for `(region)`, then for `()`, which is everything. The output stacks them. The subtotal row for a region has `city` set to NULL, and the grand total row has both columns NULL.\n\n`CUBE (region, channel)` goes further and produces every combination: by region and channel, by region alone, by channel alone, and the total. `GROUPING SETS ((region, city), (channel), ())` lets you name exactly which piles you want and skip the rest, which is what you reach for when CUBE would produce thirty subtotals nobody reads.\n\nPostgres, DuckDB, SQL Server, Oracle and BigQuery support all three. MySQL has `GROUP BY region, city WITH ROLLUP` and nothing else. SQLite has none of them, and the fallback is `UNION ALL` of the separate grouped queries, which is more typing and the same result.\n\n## The NULL that is not a NULL\n\nThe subtotal rows use NULL to mean \"all cities\". That collides head-on with a real NULL: a sale whose `city` was never recorded also produces a row with `city` NULL, at the detail grain. In the output the two rows look identical, and a report that labels every NULL city as \"Total\" has just relabelled its missing data as a subtotal.\n\n`GROUPING()` tells them apart. It returns 1 when the column was rolled up in that row, and 0 when the row is at the detail grain and the NULL is genuine:\n\n```sql\nSELECT\n  CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE region END AS region,\n  CASE WHEN GROUPING(city)   = 1 THEN 'All cities'  ELSE city   END AS city,\n  sum(amount) AS revenue\nFROM sales\nGROUP BY ROLLUP (region, city);\n```\n\nNow a real unknown city stays NULL and a subtotal is labelled as one. In a report with any nullable grouping column, `GROUPING()` is not optional.\n\n## What the output is not\n\nThe result of a ROLLUP is not a table at one grain. It is three tables stacked, and any arithmetic across the whole result is wrong. `SELECT sum(revenue) FROM (...rollup...)` counts every sale three times: once in its city, once in its region, once in the total. Anybody who exports the result to a spreadsheet and drags a SUM over the column makes this mistake by reflex.\n\nSo a ROLLUP result is for reading, not for further computation. If a downstream step needs the numbers, give it the detail grain and let it compute its own subtotals, or give it the subtotal rows only, filtered with `WHERE GROUPING(city) = 1`.\n\n## Ordering the rows\n\nThe natural output order puts subtotals wherever the engine's hash table happened to leave them. To get the layout people expect, city rows followed by their region's subtotal and then the grand total last, sort on the grouping flags:\n\n```sql\nORDER BY GROUPING(region), region, GROUPING(city), city\n```\n\nDetail rows have flag 0 and sort first within each region; the subtotal has flag 1 and follows; the grand total has both flags set and comes last.\n\n## When to use it\n\nROLLUP earns its place in reports that will be read as a document: a printed monthly summary, a PDF for a board, an exported sheet. It costs one scan instead of three and keeps the subtotals consistent with the detail by construction, because they are computed from the same filtered rows in the same statement. It does not belong in a query that feeds a chart or another query, where a single grain is what the consumer expects and a stacked one will be silently misread.\n\nGROUPING SETS is the version to remember for the common request of \"totals by region and, separately, totals by channel\": it produces exactly those two breakdowns from one scan without the full region-by-channel grid that CUBE would add in between."},"check":{"question":"A ROLLUP report by region and city is exported to a spreadsheet, and a colleague sums the revenue column to get a company total. The figure is roughly three times the true total. Why?","options":[{"text":"ROLLUP adds a row per city per region, so cities that appear in more than one region are counted again for each.","correct":false,"tempting":"A city belongs to one region in the grouping; the multiplication comes from the extra grains, not from cities repeating."},{"text":"The output stacks the city grain, the region subtotals and the grand total, so each sale is counted once at every level.","correct":true},{"text":"The NULL city rows for unrecorded cities were treated as subtotals and included twice.","correct":false,"tempting":"Mislabelled NULLs affect a handful of rows and cannot triple a total; the tripling is structural."},{"text":"Spreadsheets treat NULL as zero and the exported subtotals lost their grouping flags, so the sum silently included blank rows as well as real ones.","correct":false,"tempting":"Blank cells add nothing to a sum; the extra amount comes from real subtotal rows carrying real revenue."}]},"prev":{"slug":"the-row-behind-the-max","title":"The row behind the MAX: which order was the biggest","url":"https://addaly.com/learn/data-and-sql/the-row-behind-the-max"},"next":{"slug":"pivoting-rows-to-columns","title":"Wide and long: pivoting rows to columns and back","url":"https://addaly.com/learn/data-and-sql/pivoting-rows-to-columns"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Subtotals and grand totals in one query: ROLLUP and GROUPING', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/rollups-and-subtotals (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Subtotals and grand totals in one query: ROLLUP and GROUPING","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"ROLLUP stacks several grains in one result, so use GROUPING() to tell a subtotal’s NULL from a real one and never sum across the output, which counts each row once per level.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"three-grains-in-one-report","runs":[{"text":"Three grains in one report"}]},{"t":"p","runs":[{"text":"A sales report wants revenue by city, a subtotal for each region, and a grand total at the bottom. That is three queries with three different GROUP BY clauses, and three scans of the table. Or it is one:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT region, city, sum(amount) AS revenue\nFROM sales\nGROUP BY ROLLUP (region, city)\nORDER BY region, city;"},{"t":"p","runs":[{"text":"ROLLUP (region, city)","code":true},{"text":" produces the piles for "},{"text":"(region, city)","code":true},{"text":", then for "},{"text":"(region)","code":true},{"text":", then for "},{"text":"()","code":true},{"text":", which is everything. The output stacks them. The subtotal row for a region has "},{"text":"city","code":true},{"text":" set to NULL, and the grand total row has both columns NULL."}]},{"t":"p","runs":[{"text":"CUBE (region, channel)","code":true},{"text":" goes further and produces every combination: by region and channel, by region alone, by channel alone, and the total. "},{"text":"GROUPING SETS ((region, city), (channel), ())","code":true},{"text":" lets you name exactly which piles you want and skip the rest, which is what you reach for when CUBE would produce thirty subtotals nobody reads."}]},{"t":"p","runs":[{"text":"Postgres, DuckDB, SQL Server, Oracle and BigQuery support all three. MySQL has "},{"text":"GROUP BY region, city WITH ROLLUP","code":true},{"text":" and nothing else. SQLite has none of them, and the fallback is "},{"text":"UNION ALL","code":true},{"text":" of the separate grouped queries, which is more typing and the same result."}]},{"t":"h2","id":"the-null-that-is-not-a-null","runs":[{"text":"The NULL that is not a NULL"}]},{"t":"p","runs":[{"text":"The subtotal rows use NULL to mean “all cities”. That collides head-on with a real NULL: a sale whose "},{"text":"city","code":true},{"text":" was never recorded also produces a row with "},{"text":"city","code":true},{"text":" NULL, at the detail grain. In the output the two rows look identical, and a report that labels every NULL city as “Total” has just relabelled its missing data as a subtotal."}]},{"t":"p","runs":[{"text":"GROUPING()","code":true},{"text":" tells them apart. It returns 1 when the column was rolled up in that row, and 0 when the row is at the detail grain and the NULL is genuine:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE region END AS region,\n  CASE WHEN GROUPING(city)   = 1 THEN 'All cities'  ELSE city   END AS city,\n  sum(amount) AS revenue\nFROM sales\nGROUP BY ROLLUP (region, city);"},{"t":"p","runs":[{"text":"Now a real unknown city stays NULL and a subtotal is labelled as one. In a report with any nullable grouping column, "},{"text":"GROUPING()","code":true},{"text":" is not optional."}]},{"t":"h2","id":"what-the-output-is-not","runs":[{"text":"What the output is not"}]},{"t":"p","runs":[{"text":"The result of a ROLLUP is not a table at one grain. It is three tables stacked, and any arithmetic across the whole result is wrong. "},{"text":"SELECT sum(revenue) FROM (...rollup...)","code":true},{"text":" counts every sale three times: once in its city, once in its region, once in the total. Anybody who exports the result to a spreadsheet and drags a SUM over the column makes this mistake by reflex."}]},{"t":"p","runs":[{"text":"So a ROLLUP result is for reading, not for further computation. If a downstream step needs the numbers, give it the detail grain and let it compute its own subtotals, or give it the subtotal rows only, filtered with "},{"text":"WHERE GROUPING(city) = 1","code":true},{"text":"."}]},{"t":"h2","id":"ordering-the-rows","runs":[{"text":"Ordering the rows"}]},{"t":"p","runs":[{"text":"The natural output order puts subtotals wherever the engine’s hash table happened to leave them. To get the layout people expect, city rows followed by their region’s subtotal and then the grand total last, sort on the grouping flags:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ORDER BY GROUPING(region), region, GROUPING(city), city"},{"t":"p","runs":[{"text":"Detail rows have flag 0 and sort first within each region; the subtotal has flag 1 and follows; the grand total has both flags set and comes last."}]},{"t":"h2","id":"when-to-use-it","runs":[{"text":"When to use it"}]},{"t":"p","runs":[{"text":"ROLLUP earns its place in reports that will be read as a document: a printed monthly summary, a PDF for a board, an exported sheet. It costs one scan instead of three and keeps the subtotals consistent with the detail by construction, because they are computed from the same filtered rows in the same statement. It does not belong in a query that feeds a chart or another query, where a single grain is what the consumer expects and a stacked one will be silently misread."}]},{"t":"p","runs":[{"text":"GROUPING SETS is the version to remember for the common request of “totals by region and, separately, totals by channel”: it produces exactly those two breakdowns from one scan without the full region-by-channel grid that CUBE would add in between."}]}],"check":{"question":[{"text":"A ROLLUP report by region and city is exported to a spreadsheet, and a colleague sums the revenue column to get a company total. The figure is roughly three times the true total. Why?"}],"options":[{"text":[{"text":"ROLLUP adds a row per city per region, so cities that appear in more than one region are counted again for each."}],"correct":false,"tempting":[{"text":"A city belongs to one region in the grouping; the multiplication comes from the extra grains, not from cities repeating."}]},{"text":[{"text":"The output stacks the city grain, the region subtotals and the grand total, so each sale is counted once at every level."}],"correct":true,"tempting":null},{"text":[{"text":"The NULL city rows for unrecorded cities were treated as subtotals and included twice."}],"correct":false,"tempting":[{"text":"Mislabelled NULLs affect a handful of rows and cannot triple a total; the tripling is structural."}]},{"text":[{"text":"Spreadsheets treat NULL as zero and the exported subtotals lost their grouping flags, so the sum silently included blank rows as well as real ones."}],"correct":false,"tempting":[{"text":"Blank cells add nothing to a sum; the extra amount comes from real subtotal rows carrying real revenue."}]}],"order":[3,2,1,0],"why":null},"prev":{"slug":"the-row-behind-the-max","title":"The row behind the MAX: which order was the biggest","minutes":9},"next":{"slug":"pivoting-rows-to-columns","title":"Wide and long: pivoting rows to columns and back","minutes":9}}}