Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 125 ยท Aggregation

Subtotals: ROLLUP, CUBE and GROUPING SETS

Add subtotal and grand-total rows to SQL reports with ROLLUP, CUBE and GROUPING SETS, use GROUPING() to label them, and build the same reports in SQLite with UNION ALL.

A grouped report shows one row per group. Spreadsheets and printed reports usually add more: a subtotal after each section and a grand total at the bottom. You could compute those in application code, but SQL can produce them itself, in the same result set as the detail rows.

The SQL standard has three tools for this, all extensions of GROUP BY: ROLLUP, CUBE and GROUPING SETS. PostgreSQL, SQL Server and Oracle support all three, and MySQL supports ROLLUP. SQLite supports none of them, so in this lesson you'll read each feature in its standard form, see exactly what it returns, and then build the same result in SQLite with UNION ALL, which works in every database.

The idea: several groupings in one result

Every GROUP BY uses one grouping set: the list of columns it groups by. GROUP BY category uses the set (category). A grand total is also a grouping, just an empty one: (), one group containing every row.

Subtotal features let one query use several grouping sets at once and stack the results:

You write Grouping sets it means
GROUP BY ROLLUP (a, b) (a, b), (a), ()
GROUP BY CUBE (a, b) (a, b), (a), (b), ()
GROUP BY GROUPING SETS ((a), (b)) exactly (a) and (b)

In the rows produced for a coarser set, the columns that aren't part of that set come back as NULL. In a (a) subtotal row, b is NULL; in the () grand-total row, both are NULL.

A grand total with ROLLUP

Start with one level: inventory value (price ร— stock) per category, plus a grand total. In PostgreSQL, SQL Server or Oracle you write:

SQL
-- PostgreSQL, SQL Server, Oracle
SELECT
  c.name AS category,
  SUM(p.price * p.stock) AS stock_value
FROM products AS p
JOIN categories AS c ON c.id = p.category_id
GROUP BY ROLLUP (c.name)
ORDER BY c.name;

ROLLUP (c.name) means the grouping sets (c.name) and (): one row per category, then one extra row for all products, whose category is NULL. MySQL spells the same thing with a suffix:

SQL
-- MySQL
SELECT c.name AS category, SUM(p.price * p.stock) AS stock_value
FROM products AS p
JOIN categories AS c ON c.id = p.category_id
GROUP BY c.name WITH ROLLUP;

In SQLite, write each grouping set as its own SELECT and glue them together with UNION ALL, which appends the rows of the second query to the first. (You'll study it fully in UNION, INTERSECT and EXCEPT.) Both halves must return the same number of columns:

SQL Categories plus a grand total ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns six rows, the second returns one: 64,051.70 worth of stock in total. Instead of NULL the SQLite version prints a clear label, which is one advantage of building the rows yourself. The column names come from the first SELECT.

Two levels: subtotals inside a hierarchy

ROLLUP shines when the columns form a hierarchy, like quarter โ†’ month. ROLLUP (quarter, month) gives a row per month, a subtotal per quarter and a grand total:

SQL
-- PostgreSQL
-- (assuming ordered_at is a real timestamp column)
SELECT
  EXTRACT(QUARTER FROM o.ordered_at) AS quarter,
  EXTRACT(MONTH FROM o.ordered_at) AS month,
  SUM(i.quantity * i.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY ROLLUP (
  EXTRACT(QUARTER FROM o.ordered_at),
  EXTRACT(MONTH FROM o.ordered_at)
)
ORDER BY quarter, month;

The order inside ROLLUP matters: it rolls up from right to left. ROLLUP (quarter, month) drops month first, then quarter. ROLLUP (month, quarter) would instead give each month a "subtotal" that just repeats the month's own row, and no quarter subtotals at all.

In PostgreSQL the subtotal rows have NULL in month (and the grand total also in quarter), and ORDER BY places those NULLs after the real values because PostgreSQL sorts NULL last in ascending order. SQL Server and MySQL sort NULL first, so their subtotals would appear above the months unless you sort by GROUPING(), which you'll meet below.

The SQLite version needs three grouping sets, so it repeats the base data three times. A WITH clause (Common Table Expressions) names that base data once, as sales, so each SELECT stays short:

SQL Months, quarter subtotals and a total ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Seventeen rows come back: 12 months, 4 quarter subtotals and the year's total of 26,515.90. The final ORDER BY applies to the whole union. It puts each quarter's 'subtotal' row right after its months, because the text 'subtotal' sorts after '01'โ€ฆ'12', and 'total' sorts after 'Q4'. Relying on label spelling for sort order is fragile, though. A more robust pattern adds a numeric sort column, as you'll see below.

Totals of totals. Each subtotal is computed from the detail data, not by adding up rounded month values. That's what ROLLUP does too, and it's why the rows always agree with a direct SUM over the same data.

CUBE: every combination

ROLLUP assumes a hierarchy. When two columns are independent, like quarter and order status, you might want totals along both dimensions: per quarter (all statuses), per status (all quarters), and overall. That's CUBE. For n columns it produces all 2โฟ grouping sets:

SQL
-- PostgreSQL, SQL Server, Oracle (not MySQL)
SELECT quarter, status, COUNT(*) AS orders
FROM orders_with_quarter  -- a view that adds a quarter column
GROUP BY CUBE (quarter, status);

Here's the SQLite equivalent. To keep the result short, it only looks at delivered and cancelled orders. Each SELECT handles one grouping set and adds a grouping_set label so you can see where each row comes from:

SQL CUBE with UNION ALL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You get 8 detail rows (4 quarters ร— 2 statuses), 4 quarter totals, 2 status totals and 1 grand total: 15 rows that a spreadsheet would show as the cells, row totals, column totals and corner of a cross-tab. To lay this out as a real grid with statuses as columns, see Pivoting Rows into Columns.

GROUPING SETS: pick exactly the groupings you want

ROLLUP and CUBE are shorthands. GROUPING SETS is the general form: you list the sets yourself. A common use is a "summary page" with several unrelated breakdowns in one result:

SQL
-- PostgreSQL, SQL Server, Oracle
SELECT status, quarter, COUNT(*) AS orders
FROM orders_with_quarter  -- a view that adds a quarter column
GROUP BY GROUPING SETS ((status), (quarter), ());

This returns orders per status, orders per quarter and the overall count, without the status ร— quarter combinations. In fact ROLLUP (a, b) is just GROUPING SETS ((a, b), (a), ()), and the SQLite translation is always the same: one SELECT โ€ฆ GROUP BY per grouping set, joined with UNION ALL.

SQL Grouping sets in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The two breakdowns share one value column, so the shipping fee is formatted as text with printf to match the text status. Every group of each breakdown adds up to the same 120 orders.

Telling subtotal NULLs from real NULLs

There's a catch in the standard features: a subtotal row marks the rolled-up column with NULL, but the data itself may contain NULLs too. In the school database, four students have no homeroom. ROLLUP (homeroom) would return two rows with a NULL homeroom: the students without one, and the grand total.

The GROUPING() function solves this. GROUPING(col) returns 1 when the row is a subtotal in which col was rolled up, and 0 when col is a real grouping value (even a NULL one):

SQL
-- PostgreSQL, SQL Server, Oracle, MySQL 8
SELECT
  CASE
    WHEN GROUPING(homeroom) = 1 THEN 'All students'
    ELSE COALESCE(homeroom, 'No homeroom')
  END AS homeroom,
  COUNT(*) AS students
FROM students
GROUP BY ROLLUP (homeroom)
ORDER BY GROUPING(homeroom), homeroom;

In SQLite you control each part of the union, so add your own grouping flag. It doubles as a reliable sort key:

SQL A grouping flag for labels and sorting ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The real NULL group appears as "No homeroom" with 4 students, and the total of 36 always sorts last because is_total is 1. SQL Server also has GROUPING_ID(a, b, โ€ฆ), which packs the GROUPING() bits of several columns into one integer, handy for sorting multi-level reports.

A total as a column instead of a row

Sometimes you don't want an extra row at all, just the total next to each group, for example to show percentages. A window function does that without UNION ALL, as you saw in Grouping in Practice:

SQL Total as a column ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Use subtotal rows for printed or exported reports, and total columns when each row needs to be compared with the total.

Support across databases

Feature PostgreSQL SQL Server Oracle MySQL 8 SQLite
ROLLUP ROLLUP (a, b) ROLLUP (a, b) ROLLUP (a, b) a, b WITH ROLLUP No (use UNION ALL)
CUBE Yes Yes Yes No No
GROUPING SETS Yes Yes Yes No No
GROUPING() Yes Yes (+ GROUPING_ID) Yes (+ GROUPING_ID) Yes Not needed: add a flag column

MariaDB supports WITH ROLLUP like MySQL. Older SQL Server code sometimes uses GROUP BY a, b WITH ROLLUP and WITH CUBE; that syntax is deprecated, so prefer the standard form.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which grouping sets does GROUP BY ROLLUP (region, city) produce?

    ROLLUP removes columns from right to left, ending with the empty set for the grand total.
  2. 2. How many grouping sets does CUBE (a, b, c) produce?

    CUBE uses every subset of its columns, which is 2 to the power of 3, so 8 grouping sets.
  3. 3. SQLite has no ROLLUP. How do you add a grand-total row to a grouped query?

    Each grouping set becomes its own SELECT. The grand total is a SELECT with no GROUP BY, appended with UNION ALL.
  4. 4. Why does the GROUPING() function exist?

    Both a rolled-up column and a genuine NULL group show NULL. GROUPING(col) returns 1 only for the rolled-up case.

Summary

  • A grouping set is the list of columns a grouping uses; () is the grand total.
  • ROLLUP (a, b) = sets (a, b), (a), (): subtotals for a hierarchy. Column order matters.
  • CUBE (a, b) = every combination: (a, b), (a), (b), ().
  • GROUPING SETS (โ€ฆ) lists exactly the sets you want; the other two are shorthands for it.
  • Rolled-up columns are NULL; GROUPING(col) distinguishes them from real NULLs.
  • In SQLite (or anywhere), write one SELECT per grouping set and combine them with UNION ALL; add a flag column for labels and sorting.

Next: Pivoting Rows into Columns, where you'll turn grouped rows into a cross-tab grid.