Subtotals with ROLLUP, CUBE and GROUPING SETS

A sales report rarely stops at one level of detail. It lists revenue per city, then a subtotal for each state, then a grand total at the bottom. Each level is an ordinary GROUP BY with a different list of columns, and SQL can compute all of them in one query. ROLLUP (state, city) adds a subtotal for every state and a grand total to the rows per city. CUBE (month, method) adds totals for every combination of its columns: per month, per method and overall. GROUPING SETS lists exactly the groupings you want. In every total row, the columns that were added across are NULL, and the GROUPING() function tells those rows from rows whose value really is NULL.

PostgreSQL has all three. MySQL has ROLLUP only. SQLite has none of them, but the same rows come from a few grouped queries stacked with UNION ALL, and that is how every example below runs in your browser, on SQLite 3.49.1 through sql.js 1.14.2.

Three totals, three groupings

A grouping set is the list of columns that one GROUP BY groups by. The report above needs three of them:

• (state, city): one row per city;
• (state): one row per state, the subtotal;
• (), the empty set: all rows in one group, the grand total, like an aggregate query without GROUP BY.

ROLLUP (state, city) is a short way to ask for exactly those three. It keeps the whole list, then drops columns from the right one at a time until none is left, so ROLLUP (year, month, day) gives four sets: (year, month, day), (year, month), (year) and (). List the columns from the coarsest level to the finest, the way a report nests them.

ROLLUP in PostgreSQL and MySQL

Here is the query, written for PostgreSQL. SQLite has no ROLLUP, and its answer shows how it read the clause:

-- Eight orders of an online shop; amounts are in paise (100 paise make a rupee).
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 149900),
  (2, 'Karnataka',   'Bengaluru',  45000),
  (3, 'Karnataka',   'Mysuru',     32000),
  (4, 'Maharashtra', 'Mumbai',     89900),
  (5, 'Maharashtra', 'Mumbai',    120000),
  (6, 'Maharashtra', 'Pune',       64900),
  (7, 'Tamil Nadu',  'Chennai',    99900),
  (8, 'Karnataka',   'Bengaluru',  25000);

-- PostgreSQL, and MySQL from 8.4.3: every city, a subtotal for each state and a grand
-- total, in one query. SQLite has no ROLLUP and reads it as a call to a function.
SELECT state, city,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees
FROM orders
GROUP BY ROLLUP (state, city)
ORDER BY state, city;

Parse error near line 20: no such function: ROLLUP

SQLite took ROLLUP (state, city) for a call to a function named ROLLUP, and there is no such function. In PostgreSQL 18 the same query returns nine rows: the five cities, a subtotal for each of the three states and the grand total. In the total rows, the column that was added across is NULL: city in the three subtotals, both columns in the grand total.

MySQL spells the same request GROUP BY state, city WITH ROLLUP, with the modifier after the list. MySQL calls the extra rows super-aggregate rows.

The same rows in SQLite, with UNION ALL

PostgreSQL’s manual describes a GROUP BY with grouping sets as equivalent to a UNION ALL of queries that each group by one of the sets. That gives a version that runs anywhere. UNION ALL stacks the rows of two queries that return the same number of columns, and the column names come from the first query (a later lesson on set operations covers it in full). So write one SELECT per grouping set, and put NULL where a set has no column:

-- The same eight orders; amounts are in paise (100 paise make a rupee).
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 149900),
  (2, 'Karnataka',   'Bengaluru',  45000),
  (3, 'Karnataka',   'Mysuru',     32000),
  (4, 'Maharashtra', 'Mumbai',     89900),
  (5, 'Maharashtra', 'Mumbai',    120000),
  (6, 'Maharashtra', 'Pune',       64900),
  (7, 'Tamil Nadu',  'Chennai',    99900),
  (8, 'Karnataka',   'Bengaluru',  25000);

-- ROLLUP (state, city) groups the rows three ways: by (state, city), by (state) and not
-- at all. One SELECT per grouping, stacked with UNION ALL. g_state and g_city are 1 where
-- that column was rolled up into a total, as GROUPING() reports it in PostgreSQL and MySQL.
SELECT state, city,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees,
       0 AS g_state, 0 AS g_city
FROM orders
GROUP BY state, city
UNION ALL
SELECT state, NULL, count(*), sum(amount_paise) / 100.0, 0, 1
FROM orders
GROUP BY state
UNION ALL
SELECT NULL, NULL, count(*), sum(amount_paise) / 100.0, 1, 1
FROM orders
-- Totals after the rows they add up: sort on the markers before each column.
ORDER BY g_state, state, g_city, city;

┌─────────────┬───────────┬────────┬────────────────┬─────────┬────────┐
│    state    │   city    │ orders │ revenue_rupees │ g_state │ g_city │
├─────────────┼───────────┼────────┼────────────────┼─────────┼────────┤
│ Karnataka   │ Bengaluru │ 3      │ 2199.0         │ 0       │ 0      │
│ Karnataka   │ Mysuru    │ 1      │ 320.0          │ 0       │ 0      │
│ Karnataka   │           │ 4      │ 2519.0         │ 0       │ 1      │
│ Maharashtra │ Mumbai    │ 2      │ 2099.0         │ 0       │ 0      │
│ Maharashtra │ Pune      │ 1      │ 649.0          │ 0       │ 0      │
│ Maharashtra │           │ 3      │ 2748.0         │ 0       │ 1      │
│ Tamil Nadu  │ Chennai   │ 1      │ 999.0          │ 0       │ 0      │
│ Tamil Nadu  │           │ 1      │ 999.0          │ 0       │ 1      │
│             │           │ 8      │ 6266.0         │ 1       │ 1      │
└─────────────┴───────────┴────────┴────────────────┴─────────┴────────┘

These are the nine rows that PostgreSQL returns for the ROLLUP query. The first SELECT is the plain GROUP BY you already know. The second groups by state alone and writes NULL for the city. The third has no GROUP BY, so it always returns exactly one row, even for an empty table. A quick check that nothing went missing: the three subtotals, 2519.0 + 2748.0 + 999.0, add up to the grand total of 6266.0. A state with a single city still gets its subtotal, which is why Tamil Nadu’s 999.0 appears twice.

The two extra columns, g_state and g_city, are markers: 1 where that column was added up into a total, 0 where the row is grouped by it. They are what GROUPING() returns in PostgreSQL and MySQL, as a later section shows, and here they also decide the order.

Two rules of SQLite’s compound queries shape this code. Only the last SELECT may have an ORDER BY, and it sorts the whole result; and its terms must be output columns, which is one more reason the markers are columns of the result. The emulation also has a cost: it is three separate queries, so it reads the table three times.

Totals in the right place

Sorting by the grouped columns alone looks like it should work. In SQLite it does not:

-- The same eight orders; amounts are in paise (100 paise make a rupee).
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 149900),
  (2, 'Karnataka',   'Bengaluru',  45000),
  (3, 'Karnataka',   'Mysuru',     32000),
  (4, 'Maharashtra', 'Mumbai',     89900),
  (5, 'Maharashtra', 'Mumbai',    120000),
  (6, 'Maharashtra', 'Pune',       64900),
  (7, 'Tamil Nadu',  'Chennai',    99900),
  (8, 'Karnataka',   'Bengaluru',  25000);

-- The same three groupings, sorted only by state and city. SQLite puts NULLs first in
-- an ascending sort, so every total lands above the rows it adds up.
SELECT state, city, count(*) AS orders, sum(amount_paise) / 100.0 AS revenue_rupees
FROM orders
GROUP BY state, city
UNION ALL
SELECT state, NULL, count(*), sum(amount_paise) / 100.0
FROM orders
GROUP BY state
UNION ALL
SELECT NULL, NULL, count(*), sum(amount_paise) / 100.0
FROM orders
ORDER BY state, city;

┌─────────────┬───────────┬────────┬────────────────┐
│    state    │   city    │ orders │ revenue_rupees │
├─────────────┼───────────┼────────┼────────────────┤
│             │           │ 8      │ 6266.0         │
│ Karnataka   │           │ 4      │ 2519.0         │
│ Karnataka   │ Bengaluru │ 3      │ 2199.0         │
│ Karnataka   │ Mysuru    │ 1      │ 320.0          │
│ Maharashtra │           │ 3      │ 2748.0         │
│ Maharashtra │ Mumbai    │ 2      │ 2099.0         │
│ Maharashtra │ Pune      │ 1      │ 649.0          │
│ Tamil Nadu  │           │ 1      │ 999.0          │
│ Tamil Nadu  │ Chennai   │ 1      │ 999.0          │
└─────────────┴───────────┴────────┴────────────────┘

The grand total comes first, and every state’s subtotal sits above its cities. SQLite treats NULL as smaller than any other value, so NULLs come first in an ascending sort, and MySQL sorts them the same way. PostgreSQL treats NULL as larger, so there the same ORDER BY state, city happens to put each total last; the ORDER BY lesson showed all three rules. Do not rely on it. Sort on the markers before each column, as the UNION ALL example does with ORDER BY g_state, state, g_city, city. In PostgreSQL and MySQL the markers come from the function itself: ORDER BY GROUPING(state), state, GROUPING(city), city puts every subtotal after its cities and the grand total last.

Without any ORDER BY, the order of the rows is whatever the engine finds fastest, as PostgreSQL’s manual says of every query, so a report with totals always needs one.

Subtotal or a real NULL: GROUPING()

The NULL in a total row means “all cities”. A NULL in the data means “no city recorded”. Add one order that was collected at a warehouse counter and has no city, and both kinds of NULL appear in the same result:

-- The eight orders, plus order 9: collected at a warehouse counter, so it has no city.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 149900),
  (2, 'Karnataka',   'Bengaluru',  45000),
  (3, 'Karnataka',   'Mysuru',     32000),
  (4, 'Maharashtra', 'Mumbai',     89900),
  (5, 'Maharashtra', 'Mumbai',    120000),
  (6, 'Maharashtra', 'Pune',       64900),
  (7, 'Tamil Nadu',  'Chennai',    99900),
  (8, 'Karnataka',   'Bengaluru',  25000),
  (9, 'Tamil Nadu',  NULL,         40000);

-- The ROLLUP rows again. Tamil Nadu now has two rows with an empty city: the group of
-- orders that really have no city, and the state's subtotal. Only g_city tells them apart.
SELECT state, city,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees,
       0 AS g_state, 0 AS g_city
FROM orders
GROUP BY state, city
UNION ALL
SELECT state, NULL, count(*), sum(amount_paise) / 100.0, 0, 1
FROM orders
GROUP BY state
UNION ALL
SELECT NULL, NULL, count(*), sum(amount_paise) / 100.0, 1, 1
FROM orders
ORDER BY g_state, state, g_city, city;

┌─────────────┬───────────┬────────┬────────────────┬─────────┬────────┐
│    state    │   city    │ orders │ revenue_rupees │ g_state │ g_city │
├─────────────┼───────────┼────────┼────────────────┼─────────┼────────┤
│ Karnataka   │ Bengaluru │ 3      │ 2199.0         │ 0       │ 0      │
│ Karnataka   │ Mysuru    │ 1      │ 320.0          │ 0       │ 0      │
│ Karnataka   │           │ 4      │ 2519.0         │ 0       │ 1      │
│ Maharashtra │ Mumbai    │ 2      │ 2099.0         │ 0       │ 0      │
│ Maharashtra │ Pune      │ 1      │ 649.0          │ 0       │ 0      │
│ Maharashtra │           │ 3      │ 2748.0         │ 0       │ 1      │
│ Tamil Nadu  │           │ 1      │ 400.0          │ 0       │ 0      │
│ Tamil Nadu  │ Chennai   │ 1      │ 999.0          │ 0       │ 0      │
│ Tamil Nadu  │           │ 2      │ 1399.0         │ 0       │ 1      │
│             │           │ 9      │ 6666.0         │ 1       │ 1      │
└─────────────┴───────────┴────────┴────────────────┴─────────┴────────┘

Tamil Nadu now has two rows with an empty city. The first is a real group: the one order without a city, for 400.0. The third is the state’s subtotal, for 1399.0, which adds that order and Chennai’s. Testing city IS NULL cannot tell them apart; only the marker can.

That is the job of GROUPING() in PostgreSQL and MySQL. GROUPING(city) returns 1 on a row where city was rolled up into a total and 0 on every other row, including the group whose city really is NULL. With several arguments it returns a bitmask, and the rightmost argument is the lowest bit: GROUPING(state, city) is 0 on a city row, 1 on a state subtotal and 3 on the grand total. Its arguments must match expressions of the GROUP BY clause exactly.

Like an aggregate, GROUPING() belongs to the grouped result. You can use it in the SELECT list, in HAVING and in ORDER BY, but not in WHERE, which runs before any group exists. HAVING GROUPING(city) = 1 keeps only the total rows, and HAVING GROUPING(state) = 0 drops the grand total.

Labels instead of NULLs

People reading a report should see “All cities”, not an empty cell. In the UNION ALL version each SELECT knows the level it computes, so it can write its own label, and a missing city can get a different one:

-- The nine orders; order 9 has no city.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 149900),
  (2, 'Karnataka',   'Bengaluru',  45000),
  (3, 'Karnataka',   'Mysuru',     32000),
  (4, 'Maharashtra', 'Mumbai',     89900),
  (5, 'Maharashtra', 'Mumbai',    120000),
  (6, 'Maharashtra', 'Pune',       64900),
  (7, 'Tamil Nadu',  'Chennai',    99900),
  (8, 'Karnataka',   'Bengaluru',  25000),
  (9, 'Tamil Nadu',  NULL,         40000);

-- Labels instead of NULLs. Each SELECT knows which level it computes, so it can write
-- its own label; a missing city in the data gets a different one.
SELECT state, coalesce(city, '(no city)') AS city,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees,
       0 AS g_state, 0 AS g_city
FROM orders
GROUP BY state, city
UNION ALL
SELECT state, 'All cities', count(*), sum(amount_paise) / 100.0, 0, 1
FROM orders
GROUP BY state
UNION ALL
SELECT 'All states', 'All cities', count(*), sum(amount_paise) / 100.0, 1, 1
FROM orders
ORDER BY g_state, state, g_city, city;

┌─────────────┬────────────┬────────┬────────────────┬─────────┬────────┐
│    state    │    city    │ orders │ revenue_rupees │ g_state │ g_city │
├─────────────┼────────────┼────────┼────────────────┼─────────┼────────┤
│ Karnataka   │ Bengaluru  │ 3      │ 2199.0         │ 0       │ 0      │
│ Karnataka   │ Mysuru     │ 1      │ 320.0          │ 0       │ 0      │
│ Karnataka   │ All cities │ 4      │ 2519.0         │ 0       │ 1      │
│ Maharashtra │ Mumbai     │ 2      │ 2099.0         │ 0       │ 0      │
│ Maharashtra │ Pune       │ 1      │ 649.0          │ 0       │ 0      │
│ Maharashtra │ All cities │ 3      │ 2748.0         │ 0       │ 1      │
│ Tamil Nadu  │ (no city)  │ 1      │ 400.0          │ 0       │ 0      │
│ Tamil Nadu  │ Chennai    │ 1      │ 999.0          │ 0       │ 0      │
│ Tamil Nadu  │ All cities │ 2      │ 1399.0         │ 0       │ 1      │
│ All states  │ All cities │ 9      │ 6666.0         │ 1       │ 1      │
└─────────────┴────────────┴────────┴────────────────┴─────────┴────────┘

In PostgreSQL and MySQL one SELECT produces every level, so the label must ask GROUPING(): CASE WHEN GROUPING(city) = 1 THEN 'All cities' ELSE coalesce(city, '(no city)') END. The shortcut coalesce(city, 'All cities') is a common mistake. It turns every NULL into “All cities”, so Tamil Nadu would show two rows called All cities, one for 400.0 and one for 1399.0, and a reader would add them up.

CUBE: totals for every combination

CUBE takes every subset of its columns, not only the prefixes. CUBE (month, method) stands for four grouping sets: (month, method), (month), (method) and (). The extra one, (method), is a total for each payment method across all months, which a ROLLUP (month, method) would not have:

-- The eight orders with their payment method and order date.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  method       TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 'wallet', '2026-01-04', 149900),
  (2, 'Karnataka',   'Bengaluru', 'card',   '2026-01-19',  45000),
  (3, 'Karnataka',   'Mysuru',    'wallet', '2026-02-02',  32000),
  (4, 'Maharashtra', 'Mumbai',    'cash',   '2026-01-11',  89900),
  (5, 'Maharashtra', 'Mumbai',    'wallet', '2026-02-14', 120000),
  (6, 'Maharashtra', 'Pune',      'card',   '2026-02-20',  64900),
  (7, 'Tamil Nadu',  'Chennai',   'wallet', '2026-01-27',  99900),
  (8, 'Karnataka',   'Bengaluru', 'cash',   '2026-02-09',  25000);

-- CUBE (month, method): every combination of the two columns, so four groupings:
-- (month, method), (month), (method) and ().
SELECT substr(placed_on, 1, 7)   AS month, method,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees,
       0 AS g_month, 0 AS g_method
FROM orders
GROUP BY substr(placed_on, 1, 7), method
UNION ALL
SELECT substr(placed_on, 1, 7), NULL, count(*), sum(amount_paise) / 100.0, 0, 1
FROM orders
GROUP BY substr(placed_on, 1, 7)
UNION ALL
SELECT NULL, method, count(*), sum(amount_paise) / 100.0, 1, 0
FROM orders
GROUP BY method
UNION ALL
SELECT NULL, NULL, count(*), sum(amount_paise) / 100.0, 1, 1
FROM orders
ORDER BY g_month, month, g_method, method;

┌─────────┬────────┬────────┬────────────────┬─────────┬──────────┐
│  month  │ method │ orders │ revenue_rupees │ g_month │ g_method │
├─────────┼────────┼────────┼────────────────┼─────────┼──────────┤
│ 2026-01 │ card   │ 1      │ 450.0          │ 0       │ 0        │
│ 2026-01 │ cash   │ 1      │ 899.0          │ 0       │ 0        │
│ 2026-01 │ wallet │ 2      │ 2498.0         │ 0       │ 0        │
│ 2026-01 │        │ 4      │ 3847.0         │ 0       │ 1        │
│ 2026-02 │ card   │ 1      │ 649.0          │ 0       │ 0        │
│ 2026-02 │ cash   │ 1      │ 250.0          │ 0       │ 0        │
│ 2026-02 │ wallet │ 2      │ 1520.0         │ 0       │ 0        │
│ 2026-02 │        │ 4      │ 2419.0         │ 0       │ 1        │
│         │ card   │ 2      │ 1099.0         │ 1       │ 0        │
│         │ cash   │ 2      │ 1149.0         │ 1       │ 0        │
│         │ wallet │ 4      │ 4018.0         │ 1       │ 0        │
│         │        │ 8      │ 6266.0         │ 1       │ 1        │
└─────────┴────────┴────────┴────────────────┴─────────┴──────────┘

The rows with g_month 1 and g_method 0 are the new ones: card brought 1099.0, cash 1149.0 and wallets 4018.0 across January and February. Both sets of totals add up to the same grand total of 6266.0, once by month and once by method.

In PostgreSQL the whole result is one query: the first SELECT alone, with GROUP BY CUBE (substr(placed_on, 1, 7), method) and ORDER BY GROUPING(substr(placed_on, 1, 7)), month, GROUPING(method), method. Write the expression itself inside GROUPING(): PostgreSQL accepts the output name month in GROUP BY, but not as an argument of GROUPING().

The difference in size grows fast. With n columns, ROLLUP gives n + 1 grouping sets and CUBE gives 2 to the power n: for three columns 4 against 8, for four columns 5 against 16. ROLLUP fits a hierarchy, where each column refines the one before it, such as state and city or year and month. CUBE fits independent dimensions, such as month and payment method. A CUBE (state, city) would add a total per city across all states: in this data the same five numbers as the city rows, because each city lies in one state.

MySQL has no CUBE. There, write the four SELECTs with UNION ALL, as SQLite does.

GROUPING SETS: exactly the totals you list

When neither shape fits, name the sets yourself. A dashboard that wants revenue per state and per payment method, and the overall total, but no rows for each state and method together, is GROUP BY GROUPING SETS ((state), (method), ()) in PostgreSQL:

-- The eight orders with their payment method and order date.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  state        TEXT    NOT NULL,
  city         TEXT,
  method       TEXT    NOT NULL,
  placed_on    TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Karnataka',   'Bengaluru', 'wallet', '2026-01-04', 149900),
  (2, 'Karnataka',   'Bengaluru', 'card',   '2026-01-19',  45000),
  (3, 'Karnataka',   'Mysuru',    'wallet', '2026-02-02',  32000),
  (4, 'Maharashtra', 'Mumbai',    'cash',   '2026-01-11',  89900),
  (5, 'Maharashtra', 'Mumbai',    'wallet', '2026-02-14', 120000),
  (6, 'Maharashtra', 'Pune',      'card',   '2026-02-20',  64900),
  (7, 'Tamil Nadu',  'Chennai',   'wallet', '2026-01-27',  99900),
  (8, 'Karnataka',   'Bengaluru', 'cash',   '2026-02-09',  25000);

-- GROUPING SETS ((state), (method), ()): a total per state, a total per payment method
-- and the grand total, without the state-and-method combinations a CUBE would add.
SELECT state, NULL AS method,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees,
       0 AS g_state, 1 AS g_method
FROM orders
GROUP BY state
UNION ALL
SELECT NULL, method, count(*), sum(amount_paise) / 100.0, 1, 0
FROM orders
GROUP BY method
UNION ALL
SELECT NULL, NULL, count(*), sum(amount_paise) / 100.0, 1, 1
FROM orders
ORDER BY g_state, state, g_method, method;

┌─────────────┬────────┬────────┬────────────────┬─────────┬──────────┐
│    state    │ method │ orders │ revenue_rupees │ g_state │ g_method │
├─────────────┼────────┼────────┼────────────────┼─────────┼──────────┤
│ Karnataka   │        │ 4      │ 2519.0         │ 0       │ 1        │
│ Maharashtra │        │ 3      │ 2748.0         │ 0       │ 1        │
│ Tamil Nadu  │        │ 1      │ 999.0          │ 0       │ 1        │
│             │ card   │ 2      │ 1099.0         │ 1       │ 0        │
│             │ cash   │ 2      │ 1149.0         │ 1       │ 0        │
│             │ wallet │ 4      │ 4018.0         │ 1       │ 0        │
│             │        │ 8      │ 6266.0         │ 1       │ 1        │
└─────────────┴────────┴────────┴────────────────┴─────────┴──────────┘

Seven rows: three states, three methods and the total. ROLLUP and CUBE are short forms of GROUPING SETS, and PostgreSQL lets you combine all three:

• Mix plain columns in. GROUP BY state, ROLLUP (city) gives (state, city) and (state): subtotals without a grand total. Several items in one GROUP BY combine as a cross product, each set of one with each set of the other.
• Drop repeated sets. Combinations can produce the same set twice: ROLLUP (a, b), ROLLUP (a, c) contains (a) three times, and each copy returns its rows again. GROUP BY DISTINCT, added in PostgreSQL 14, removes the duplicates.

MySQL’s ROLLUP must be the whole GROUP BY list, so it always goes down to the grand total. To drop it, filter with HAVING GROUPING(state) = 0. SQL Server and Oracle have ROLLUP, CUBE and GROUPING SETS as well; there GROUPING() takes one column, and GROUPING_ID() returns the bitmask for several. All of these come from the SQL standard, where they are feature T431, “extended grouping capabilities”, one of the features PostgreSQL lists as supported.

How the engines differ

Your turn

-- What GROUP BY ROLLUP (state, city) returns, built in SQLite: one row per city, a
-- subtotal per state and the grand total, without cancelled orders.
-- So far this gives the city rows only.
SELECT state, city,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees,
       0 AS g_state, 0 AS g_city
FROM orders
WHERE status <> 'cancelled'
GROUP BY state, city
ORDER BY g_state, state, g_city, city;

# Sample tests: each one builds a small orders table, runs your query on it and compares the rows it returns, in
# order.
tests:
  - name: one row per city, a subtotal per state and the grand total, without the cancelled order
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, state TEXT NOT NULL, city TEXT, status TEXT NOT NULL, amount_paise INTEGER NOT NULL);
      INSERT INTO orders VALUES
        (1, 'Kerala', 'Kochi',    'delivered', 50000),
        (2, 'Kerala', 'Kochi',    'delivered', 25050),
        (3, 'Kerala', 'Thrissur', 'delivered', 12000),
        (4, 'Kerala', 'Thrissur', 'cancelled', 99900),
        (5, 'Punjab', 'Amritsar', 'delivered', 30000),
        (6, 'Punjab', 'Ludhiana', 'delivered', 45000);
    expected:
      columns: [state, city, orders, revenue_rupees, g_state, g_city]
      rows:
        - ['Kerala', 'Kochi', 2, 750.5, 0, 0]
        - ['Kerala', 'Thrissur', 1, 120.0, 0, 0]
        - ['Kerala', null, 3, 870.5, 0, 1]
        - ['Punjab', 'Amritsar', 1, 300.0, 0, 0]
        - ['Punjab', 'Ludhiana', 1, 450.0, 0, 0]
        - ['Punjab', null, 2, 750.0, 0, 1]
        - [null, null, 5, 1620.5, 1, 1]
  - name: keeps orders without a city apart from the state's subtotal
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, state TEXT NOT NULL, city TEXT, status TEXT NOT NULL, amount_paise INTEGER NOT NULL);
      INSERT INTO orders VALUES
        (1, 'Goa', 'Panaji', 'delivered', 21000),
        (2, 'Goa', NULL,     'delivered', 15000),
        (3, 'Goa', NULL,     'delivered',  5000);
    expected:
      columns: [state, city, orders, revenue_rupees, g_state, g_city]
      rows:
        - ['Goa', null, 2, 200.0, 0, 0]
        - ['Goa', 'Panaji', 1, 210.0, 0, 0]
        - ['Goa', null, 3, 410.0, 0, 1]
        - [null, null, 3, 410.0, 1, 1]
  - name: still returns the grand total when every order was cancelled
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, state TEXT NOT NULL, city TEXT, status TEXT NOT NULL, amount_paise INTEGER NOT NULL);
      INSERT INTO orders VALUES
        (1, 'Assam', 'Guwahati', 'cancelled', 10000),
        (2, 'Assam', 'Silchar',  'cancelled', 32000);
    expected:
      columns: [state, city, orders, revenue_rupees, g_state, g_city]
      rows:
        - [null, null, 0, null, 1, 1]

Interview questions

Fresher · What does GROUP BY ROLLUP (a, b) return, and how is CUBE (a, b) different?

ROLLUP (a, b) returns the rows of GROUP BY a, b, plus a subtotal for each value of a and one grand total: it stands for the grouping sets (a, b), (a) and (), the list and each of its prefixes. In the total rows, the columns that were added across are NULL. CUBE (a, b) stands for every subset of its columns, (a, b), (a), (b) and (), so it also has a total for each value of b across all values of a. With n columns, ROLLUP gives n + 1 grouping sets and CUBE gives 2 to the power n, so a cube grows quickly. Use ROLLUP for a hierarchy such as year and month, or state and city, and CUBE for independent dimensions such as month and payment method. When neither fits, GROUPING SETS lists exactly the groupings you want.

Fresher · In a ROLLUP result, how do you tell a subtotal row from a row whose grouped value really is NULL?

Use GROUPING(). Both kinds of row show NULL in the column, so testing IS NULL cannot tell them apart. GROUPING(city) is 1 on a row where city was rolled up into a total, and 0 on every other row, including the group of rows whose city really is NULL. With several arguments, GROUPING(state, city) returns a bitmask whose lowest bit belongs to the rightmost argument: 0 on a detail row, 1 on a state subtotal and 3 on the grand total. It works in the SELECT list, HAVING and ORDER BY, so you can label the totals with CASE WHEN GROUPING(city) = 1 THEN …, keep only them with HAVING, or sort them after their detail rows. In SQLite, which has no GROUPING(), each SELECT of the UNION ALL version writes the marker as a constant.

Key takeaways

• A grouping set is the column list of one GROUP BY. ROLLUP (a, b) stands for (a, b), (a) and (); CUBE (a, b) adds (b); GROUPING SETS lists the sets you choose.
• In a total row, the columns added across are NULL. GROUPING() returns 1 there and 0 elsewhere, so it tells a subtotal from a real NULL; label totals with CASE and GROUPING(), never with a plain coalesce.
• PostgreSQL has all three forms and GROUP BY DISTINCT. MySQL has WITH ROLLUP, the spelling ROLLUP (…) from 8.4.3 and GROUPING(), but no CUBE or GROUPING SETS. SQLite has none of them.
• Any grouping sets can be written as grouped queries stacked with UNION ALL, one per set, with NULL for the missing columns and a constant marker for each level.
• Always sort a result with totals, and sort on the markers or on GROUPING() before each column: engines disagree on where NULLs go.