# GROUP BY, ROLLUP, CUBE and GROUPING SETS in BigQuery

> BigQuery's GROUP BY accepts SELECT aliases, column numbers and GROUP BY ALL, and computes subtotals with ROLLUP, CUBE and GROUPING SETS. Subtotal rows have NULL in the rolled-up columns; GROUPING() tells them apart from real NULLs.

Source: https://docs.querylab.io/bigquery/group-by/

Updated: 2026-10-27

`GROUP BY` collapses rows that share values into one row per group, and aggregates summarize each group. BigQuery adds conveniences on top of standard SQL (`GROUP BY ALL`, `SELECT` aliases and column numbers) and supports `ROLLUP`, `CUBE` and `GROUPING SETS`, which compute subtotals and grand totals in the same query. The examples use four rows of sales: shoes and hats in the EU and the US.

## What you can group by

Every column in `SELECT` that isn't inside an aggregate must be grouped:

**units isn't grouped or aggregated**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, units
FROM sales
GROUP BY region
```

BigQuery rejects this query:

```text
SELECT list expression references column units which is neither grouped nor aggregated at [7:16]
```

`GROUP BY ALL` groups by every `SELECT` item that isn't an aggregate, so you don't repeat the list. Querylab.io's editor offers **Use GROUP BY ALL** as a quick fix on a non-aggregated column, and suggests it when a `GROUP BY` uses position numbers (see [code quality](https://docs.querylab.io/writing-sql/code-quality/)):

**GROUP BY ALL**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY ALL
ORDER BY region, product
```

Result in BigQuery (4 rows):

| region | product | units |
| --- | --- | --- |
| eu | hats | 5 |
| eu | shoes | 10 |
| us | hats | 3 |
| us | shoes | 7 |

You can also group by a `SELECT` alias, which saves repeating an expression:

**GROUP BY region_code, an alias**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT UPPER(region) AS region_code, SUM(units) AS units
FROM sales
GROUP BY region_code
ORDER BY region_code
```

Result in BigQuery (2 rows):

| region_code | units |
| --- | --- |
| EU | 15 |
| US | 10 |

and by column position:

**GROUP BY 1, 2**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product
FROM sales
GROUP BY 1, 2
ORDER BY 1, 2
```

Result in BigQuery (4 rows):

| region | product |
| --- | --- |
| eu | hats |
| eu | shoes |
| us | hats |
| us | shoes |

Positions break silently when someone reorders the `SELECT` list, so prefer names or `GROUP BY ALL` in saved queries.

### What GROUP BY ALL skips

`GROUP BY ALL` leaves out aggregate calls and window functions. A window function runs after grouping, so it sees the grouped rows, and it stays out of the grouping key even when it sits next to plain columns:

**RANK() over the grouped totals**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT
  region,
  SUM(units) AS units,
  RANK() OVER (ORDER BY SUM(units) DESC) AS units_rank
FROM sales
GROUP BY ALL
ORDER BY region
```

Result in BigQuery (2 rows):

| region | units | units_rank |
| --- | --- | --- |
| eu | 15 | 1 |
| us | 10 | 2 |

`RANK()` ranks the two region totals. Only `region` became a grouping column.

Everything else in the `SELECT` list is grouped on, including expressions and constants. A computed column such as `units > 4` is a grouping key, so it can split a group into two rows:

**expressions and a constant in GROUP BY ALL**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT UPPER(region) AS region, units > 4 AS big_sale, 'sales' AS source, COUNT(*) AS orders
FROM sales
GROUP BY ALL
ORDER BY region, big_sale
```

Result in BigQuery (3 rows):

| region | big_sale | source | orders |
| --- | --- | --- | --- |
| EU | true | sales | 2 |
| US | false | sales | 1 |
| US | true | sales | 1 |

`US` appears twice, once for sales above 4 units and once for the rest. The `'sales'` constant is grouped on too, but one value never splits a group. If you didn't mean to group on an expression, wrap it in an aggregate or write the `GROUP BY` list yourself.

## ROLLUP: subtotals along a hierarchy

`ROLLUP(a, b)` groups by `(a, b)`, then by `(a)`, then by nothing, in one result. The rolled-up columns are NULL in the subtotal rows:

**units per region and product, per region, and in total**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY ROLLUP(region, product)
ORDER BY region NULLS LAST, product NULLS LAST
```

Result in BigQuery (7 rows):

| region | product | units |
| --- | --- | --- |
| eu | hats | 5 |
| eu | shoes | 10 |
| eu | NULL | 15 |
| us | hats | 3 |
| us | shoes | 7 |
| us | NULL | 10 |
| NULL | NULL | 25 |

The rows with `NULL` product are the region subtotals, and the row with both NULL is the grand total. The order of the columns matters: `ROLLUP(region, product)` gives region subtotals, `ROLLUP(product, region)` product subtotals.

## CUBE: every combination

`CUBE(a, b)` adds subtotals for every combination of the columns: `(a, b)`, `(a)`, `(b)` and `()`. With two columns that adds product totals across regions:

**CUBE(region, product)**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY CUBE(region, product)
ORDER BY region NULLS LAST, product NULLS LAST
```

Result in BigQuery (9 rows):

| region | product | units |
| --- | --- | --- |
| eu | hats | 5 |
| eu | shoes | 10 |
| eu | NULL | 15 |
| us | hats | 3 |
| us | shoes | 7 |
| us | NULL | 10 |
| NULL | hats | 8 |
| NULL | shoes | 17 |
| NULL | NULL | 25 |

The number of groupings doubles with each column you add, so `CUBE` over five columns computes 32 groupings. List the ones you need with `GROUPING SETS` instead.

## GROUPING SETS: listed groupings only

`GROUPING SETS` takes a list of groupings, each in parentheses, and `()` for the grand total:

**totals per region, per product, and overall**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY GROUPING SETS ((region), (product), ())
ORDER BY region NULLS LAST, product NULLS LAST
```

Result in BigQuery (5 rows):

| region | product | units |
| --- | --- | --- |
| eu | NULL | 15 |
| us | NULL | 10 |
| NULL | hats | 8 |
| NULL | shoes | 17 |
| NULL | NULL | 25 |

`ROLLUP` and `CUBE` are shorthand for common grouping sets.

## Telling a subtotal from a real NULL

If the data itself has NULLs in a grouped column, a subtotal row and a real NULL group look the same. `GROUPING(column)` returns 1 when the column was rolled up in that row and 0 when it holds a real value, including a real NULL:

**a NULL region next to the grand total**

```sql
WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
, with_null AS (SELECT * FROM sales UNION ALL SELECT NULL, 'socks', 2)
SELECT
  region,
  GROUPING(region) AS is_total_row,
  SUM(units) AS units
FROM with_null
GROUP BY ROLLUP(region)
ORDER BY is_total_row, region NULLS LAST
```

Result in BigQuery (4 rows):

| region | is_total_row | units |
| --- | --- | --- |
| eu | 0 | 15 |
| us | 0 | 10 |
| NULL | 0 | 2 |
| NULL | 1 | 27 |

Both bottom rows show `NULL` as the region. The one with `is_total_row = 0` is the 2 units sold with no region; the one with `1` is the grand total. Use `GROUPING()` to label the totals, for example `IF(GROUPING(region) = 1, 'All regions', region)`.

## Cost

Grouping doesn't change what a query reads: the bytes come from the columns you reference. `ROLLUP` and `CUBE` cost more slot time than a plain `GROUP BY`, because BigQuery computes each grouping, but nothing more in bytes. Running each grouping as a separate query bills the columns again for every query, with the 10 MB minimum each time. A `UNION ALL` of several `GROUP BY`s in one statement reads each column once, the same bytes as `GROUPING SETS`; we dry-ran both on `samples.shakespeare` and got 3.6 MB for each. See [How a query is billed](https://docs.querylab.io/bigquery/how-queries-are-billed/).

Add ROLLUP to a GROUP BY and look at the subtotal rows. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, GROUP BY clause](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_clause)

- [Aggregate functions, GROUPING](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#grouping)