---
title: "GROUPING SETS"
description: "GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query."
canonical: "https://motherduck.com/glossary/grouping-sets/"
related:
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
---

# GROUPING SETS

> GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.

## Overview
`GROUPING SETS` extends `GROUP BY` to compute aggregates over several different groupings in a single query, instead of writing multiple queries and `UNION ALL`-ing them together. Each grouping set produces its own set of aggregated rows, and all of them are combined into one result set.

```sql
SELECT region, product, SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
  (region, product),   -- revenue per region and product
  (region),            -- subtotal per region
  ()                   -- grand total across everything
);
```
Columns not included in a particular grouping set appear as `NULL` in the rows produced by that set -- for example, the `(region)` grouping set rows will have `product` as `NULL`, and the `()` (empty) set produces a single grand-total row with both `region` and `product` as `NULL`.

## ROLLUP and CUBE as shorthand
`ROLLUP` and `CUBE` are syntactic sugar for common, structured `GROUPING SETS` patterns -- `ROLLUP` for hierarchical subtotals, `CUBE` for every possible combination. `GROUPING SETS` is the more general, fully explicit form when you need a specific combination of groupings that doesn't match either shorthand's pattern.

## Distinguishing a NULL subtotal from a real NULL value
Since grouping sets produce `NULL` both for "this column wasn't part of this grouping" and for genuine `NULL` values in the data, SQL provides the `GROUPING()` function to disambiguate:

```sql
SELECT region, product, SUM(revenue) AS total_revenue,
       GROUPING(region) AS is_region_subtotal,
       GROUPING(product) AS is_product_subtotal
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());
```
`GROUPING(col)` returns `1` when the row represents a subtotal that omitted `col`, and `0` when `col` was part of that grouping.

## DuckDB notes
DuckDB implements `GROUPING SETS`, `ROLLUP`, and `CUBE` per the ANSI SQL standard. Note that this explicit grouping syntax is not compatible with DuckDB's `GROUP BY ALL` shorthand, which infers grouping columns automatically.