---
title: "ROLLUP"
description: "ROLLUP is a GROUP BY extension that produces hierarchical subtotals -- aggregating at each level of a column list, from the most detailed grouping down to a grand total."
canonical: "https://motherduck.com/glossary/rollup/"
related:
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
  - title: "EXPLAIN | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/explain/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
---

# ROLLUP

> ROLLUP is a GROUP BY extension that produces hierarchical subtotals -- aggregating at each level of a column list, from the most detailed grouping down to a grand total.

## Overview
`ROLLUP(col1, col2, ..., colN)` is shorthand for a specific, common pattern of `GROUPING SETS`: it produces one grouping set for the full column list, then progressively removes columns from the right, ending with the empty grouping set (a grand total). It's designed for hierarchical data -- like region -> city -> store -- where you want subtotals at every level plus an overall total.

```sql
SELECT region, city, SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (region, city);
```
This is equivalent to:
```sql
GROUP BY GROUPING SETS ((region, city), (region), ());
```
producing: revenue per (region, city), a subtotal per region, and a single grand-total row.

## Order matters
Unlike `CUBE`, which is symmetric, `ROLLUP`'s column order defines the hierarchy -- `ROLLUP (region, city)` assumes `city` rolls up into `region`, not the other way around. `ROLLUP (city, region)` would instead produce subtotals per city and treat region as the finer-grained dimension.

## Identifying subtotal rows
As with any `GROUPING SETS`-based query, columns omitted from a particular grouping level appear as `NULL`. Use `GROUPING(col)` to distinguish a `NULL` that represents "this is a subtotal row" from a genuine `NULL` value in the underlying data:

```sql
SELECT region, city, SUM(revenue) AS total_revenue,
       GROUPING(city) AS is_region_subtotal
FROM sales
GROUP BY ROLLUP (region, city);
```

## DuckDB notes
DuckDB implements `ROLLUP` per the ANSI SQL standard, as syntactic sugar over `GROUPING SETS`. Like `GROUPING SETS` and `CUBE`, `ROLLUP` cannot be combined with `GROUP BY ALL`.

## Typical use case
Financial and sales reporting is the classic use case: a report showing revenue by day within month within quarter, with subtotal rows rolling up at each level and a grand total at the bottom -- all from a single query rather than a report-building tool stitching several separate aggregate queries together.