SQL Academy · Lesson

ROLLUP for Subtotals

Running subtotals and grand totals.

ROLLUP for Subtotals is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is ROLLUP?

When you group data with GROUP BY, you get one row per group. But often you also want subtotals for each group and a grand total for all rows. That is exactly what ROLLUP does.

ROLLUP is an extension of GROUP BY that automatically adds extra summary rows to your result set, giving you running subtotals at each level of the grouping hierarchy and a final grand total at the end.

Setting Up Sample Data

Let us create a simple sales table to practise with. It stores the region, product category, and the sale amount for each transaction. Run the statements below to create and populate it.

CREATE TABLE sales (
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales VALUES
  ('East',  'Electronics', 500),
  ('East',  'Electronics', 300),
  ('East',  'Clothing',    150),
  ('West',  'Electronics', 700),
  ('West',  'Clothing',    200),
  ('West',  'Clothing',    100);

Plain GROUP BY First

Before using ROLLUP, let us see what a regular GROUP BY gives us. The query below returns one total per (region, category) pair — no subtotals, no grand total.

SELECT region, category, SUM(amount) AS total
FROM   sales
GROUP  BY region, category
ORDER  BY region, category;

Introducing GROUP BY ROLLUP

Replace GROUP BY with GROUP BY ROLLUP(...) and SQL will add subtotal rows automatically. The columns inside ROLLUP() form a hierarchy, and a subtotal is produced for each level of that hierarchy plus a final grand total.

SELECT region, category, SUM(amount) AS total
FROM   sales
GROUP  BY ROLLUP(region, category)
ORDER  BY region, category;

Reading the ROLLUP Output

The ROLLUP result contains three kinds of rows:

Detail rows — one row per (region, category) combination, just like plain GROUP BY.
Subtotal rows — one row per region with category = NULL, showing the regional total.
Grand total row — one row where both region and category are NULL, showing the overall total.

The NULLs act as placeholders that mark the summarised level.

Using COALESCE to Label NULLs

The NULL markers can look confusing to end users. Wrap each column in COALESCE to replace them with meaningful labels such as 'All Categories' and 'Grand Total'.

SELECT
  COALESCE(region,   'Grand Total')    AS region,
  COALESCE(category, 'All Categories') AS category,
  SUM(amount)                          AS total
FROM   sales
GROUP  BY ROLLUP(region, category)
ORDER  BY region, category;

Single-Column ROLLUP

ROLLUP works with just one column too. When you write ROLLUP(region), you get one row per region plus a single grand total row at the end. This is the simplest form and is handy for any report that needs a running total underneath the detail rows.

SELECT
  COALESCE(region, 'Grand Total') AS region,
  SUM(amount)                     AS total
FROM   sales
GROUP  BY ROLLUP(region)
ORDER  BY region;

Three-Level ROLLUP

You can pass three or more columns to ROLLUP to build deeper hierarchies. With ROLLUP(year, region, category) you would get subtotals per year+region, subtotals per year, and a grand total — each extra column adds another level of summarisation.

-- Extend the demo with a year column
SELECT
  COALESCE(CAST(yr AS TEXT), 'All Years')     AS year,
  COALESCE(region,   'All Regions')            AS region,
  COALESCE(category, 'All Categories')         AS category,
  SUM(amount)                                  AS total
FROM (
  SELECT 2024 AS yr, region, category, amount FROM sales
  UNION ALL
  SELECT 2025 AS yr, region, category, amount FROM sales
) t
GROUP  BY ROLLUP(yr, region, category)
ORDER  BY yr, region, category;

Detecting Summary Rows with GROUPING()

The GROUPING() function returns 1 when the column value in that row is a ROLLUP-generated NULL (a summary placeholder) and 0 when it is a real data value. This lets you distinguish genuine NULL data from rollup-summary NULLs.

SELECT
  region,
  category,
  SUM(amount)        AS total,
  GROUPING(region)   AS is_region_summary,
  GROUPING(category) AS is_cat_summary
FROM   sales
GROUP  BY ROLLUP(region, category)
ORDER  BY region, category;

Filtering on Summary Rows

Because ROLLUP adds rows after aggregation, you cannot filter them with WHERE. Use HAVING or wrap the whole query in a subquery. The example below keeps only the regional subtotals (rows where category is a ROLLUP NULL but region is a real value).

SELECT region, SUM(amount) AS regional_total
FROM   sales
GROUP  BY ROLLUP(region, category)
HAVING GROUPING(category) = 1
   AND GROUPING(region)   = 0
ORDER  BY region;

ROLLUP vs GROUPING SETS

ROLLUP(a, b) is shorthand for a specific set of GROUPING SETS. The two queries below are exactly equivalent — ROLLUP just saves you from writing out every grouping level by hand.

-- Using ROLLUP (concise)
SELECT region, category, SUM(amount) AS total
FROM   sales
GROUP  BY ROLLUP(region, category);

-- Equivalent explicit GROUPING SETS
SELECT region, category, SUM(amount) AS total
FROM   sales
GROUP  BY GROUPING SETS(
  (region, category),
  (region),
  ()
);

Quick Check

Test your understanding of ROLLUP with this question.

Lesson Recap

Great work! Here is a summary of what you learned about ROLLUP:

GROUP BY ROLLUP(a, b) automatically generates subtotals for each level of the column hierarchy and a grand total for all rows.
Summary rows are marked with NULL in the rolled-up columns. Use COALESCE to give them readable labels.
The GROUPING() function returns 1 for ROLLUP-generated NULLs and 0 for real data values, so you can tell them apart.
Use HAVING GROUPING(...) = 1 to filter on only the summary rows.
ROLLUP(a, b) is shorthand for GROUPING SETS((a, b), (a), ()).

ROLLUP is the go-to tool whenever your report needs subtotals and a grand total without writing complex UNION queries.

Frequently asked questions

Is the “ROLLUP for Subtotals” lesson free?

Yes — the full text of “ROLLUP for Subtotals” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “ROLLUP for Subtotals”?

Running subtotals and grand totals. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “ROLLUP for Subtotals” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy