---
title: "CUBE"
description: "CUBE is a GROUP BY extension that computes aggregates for every possible combination of a set of grouping columns, including subtotals for each subset and a grand total."
canonical: "https://motherduck.com/glossary/cube/"
related:
  - title: "Cube | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/bi-tools/cube/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
---

# CUBE

> CUBE is a GROUP BY extension that computes aggregates for every possible combination of a set of grouping columns, including subtotals for each subset and a grand total.

## Overview
`CUBE(col1, col2, ..., colN)` is shorthand for computing aggregates across every possible combination of the listed columns -- all `2^N` grouping sets, from the fully detailed grouping down to the grand total. It's the symmetric counterpart to `ROLLUP`, which only computes a hierarchical subset of combinations in a fixed order.

```sql
SELECT region, product, SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE (region, product);
```
This is equivalent to:
```sql
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  (product),
  ()
);
```
producing revenue broken down by region and product together, by region alone, by product alone, and a grand total -- all four combinations, in a single query.

## CUBE vs ROLLUP
- `ROLLUP (a, b, c)` produces `N + 1` grouping sets, following a fixed hierarchy (`(a,b,c)`, `(a,b)`, `(a)`, `()`).
- `CUBE (a, b, c)` produces `2^N` grouping sets -- every possible subset of the columns, including combinations that skip a middle column (like `(a, c)` without `b`), which `ROLLUP` never produces.

Because `CUBE`'s output grows exponentially with the number of columns, it's best used with a small number of grouping columns (2-4 is typical); a `CUBE` over many columns can produce an enormous result set.

## Identifying which combination a row belongs to
As with `ROLLUP` and `GROUPING SETS`, use `GROUPING(col)` to determine whether a given row's `NULL` represents "this column was excluded from this particular combination" versus a genuine `NULL` in the source data:

```sql
SELECT region, product, SUM(revenue) AS total_revenue,
       GROUPING(region) AS region_omitted,
       GROUPING(product) AS product_omitted
FROM sales
GROUP BY CUBE (region, product);
```

## DuckDB notes
DuckDB implements `CUBE` per the ANSI SQL standard, as syntactic sugar over `GROUPING SETS`, and like `ROLLUP` and `GROUPING SETS`, it can't be combined with `GROUP BY ALL`.