# CUBE and ROLLUP

The `CUBE` and `ROLLUP` operations let you group by different subsets of a specified set of columns in a [SELECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select.md) clause.

`CUBE(column_list)` groups by all subsets of the columns specified in `column_list`. For example, `CUBE(a, b)` groups by `(a)`, `(b)`, `(a, b)` and `()` (which represents the entire input set of rows). `ROLLUP(column_list)` groups by all subsets, including the entire set of input rows, `()`, then the leftmost column, the two leftmost columns, and so on. For example, `ROLLUP(a, b)` groups by `()`, `(a)`, and `(a, b)`.

## Syntax

Refer to the definition of the [SELECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select.md) clause for where `extended_grouping_expr` can be used.

```
extended_grouping_expr ::=
    CUBE ( col_name | expr | position}, ...)
  | ROLLUP ( col_name | expr | position}, ... )

```

## Remarks

Output rows for `CUBE` and `ROLLUP` contain a `NULL` value in the position of a grouping column if that row represents an aggregate result that does not group by that column.

## Examples

The following table contains some hypothetical sales quantities for products sold by state.

```sql
CREATE TABLE cube_sales(state VARCHAR(30), product_id INT, quantity INT);
INSERT cube_sales VALUES
  ("Oregon", 1, 10), ("Washington", 1, 15), ("California", 1, 40),
  ("Oregon", 2, 15), ("Washington", 2, 25), ("California", 2, 70);

```

This query uses `CUBE` to group by all subsets of columns in `(state, product_id)`:

```sql
SELECT state, product_id, SUM(quantity)
FROM cube_sales
GROUP BY CUBE(state, product_id)
ORDER BY state, product_id;

+------------+------------+---------------+
| state      | product_id | SUM(quantity) |
+------------+------------+---------------+
| NULL       |       NULL |           175 |
| NULL       |          1 |            65 |
| NULL       |          2 |           110 |
| California |       NULL |           110 |
| California |          1 |            40 |
| California |          2 |            70 |
| Oregon     |       NULL |            25 |
| Oregon     |          1 |            10 |
| Oregon     |          2 |            15 |
| Washington |       NULL |            40 |
| Washington |          1 |            15 |
| Washington |          2 |            25 |
+------------+------------+---------------+

```

The first row above represents the grand total. The second row represents the total for product 1 across all states. The fifth row represents the total for product 1 in California.

This query is the same as the one above but uses `ROLLUP` instead of `CUBE`:

```sql
SELECT state, product_id, SUM(quantity)
FROM cube_sales
GROUP BY ROLLUP(state, product_id)
ORDER BY state, product_id;

+------------+------------+---------------+
| state      | product_id | SUM(quantity) |
+------------+------------+---------------+
| NULL       |       NULL |           175 |
| California |       NULL |           110 |
| California |          1 |            40 |
| California |          2 |            70 |
| Oregon     |       NULL |            25 |
| Oregon     |          1 |            10 |
| Oregon     |          2 |            15 |
| Washington |       NULL |            40 |
| Washington |          1 |            15 |
| Washington |          2 |            25 |
+------------+------------+---------------+

```

Notice that the results are the same as for `CUBE` except that there is no total for `product_id` 1 and `product_id` 2 across all states.

## GROUPING() Function

The `GROUPING(column_name)` can be used in the output column list of a `SELECT` block to show whether an output row of a query with a `GROUP BY` clause has aggregated together all the input rows for different values of `column_name`. The output of `GROUPING(column_name)` is 1 if the result row has grouped together rows for `column_name` and 0 otherwise. For example:

```sql
SELECT state, product_id, SUM(quantity), GROUPING(state), GROUPING(product_id)
FROM cube_sales
GROUP BY CUBE(state, product_id)
ORDER BY state, product_id;

+------------+------------+---------------+-----------------+----------------------+
| state      | product_id | SUM(quantity) | GROUPING(state) | GROUPING(product_id) |
+------------+------------+---------------+-----------------+----------------------+
| NULL       |       NULL |           175 |               1 |                    1 |
| NULL       |          1 |            65 |               1 |                    0 |
| NULL       |          2 |           110 |               1 |                    0 |
| California |       NULL |           110 |               0 |                    1 |
| California |          1 |            40 |               0 |                    0 |
| California |          2 |            70 |               0 |                    0 |
| Oregon     |       NULL |            25 |               0 |                    1 |
| Oregon     |          1 |            10 |               0 |                    0 |
| Oregon     |          2 |            15 |               0 |                    0 |
| Washington |       NULL |            40 |               0 |                    1 |
| Washington |          1 |            15 |               0 |                    0 |
| Washington |          2 |            25 |               0 |                    0 |
+------------+------------+---------------+-----------------+----------------------+

```

The first row of output has `GROUPING(state) = 1` and `GROUPING(product_id) = 1` because the row represents the grand total, meaning that rows for all `state` and `product_id` values have been combined together to form this row. The second row has `GROUPING(state) = 1` but `GROUPING(product_id) = 0` because it represents a summary across all `state` values for a specific `product_id`.

***

Modified at: February 28, 2023

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/cube-and-rollup/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/cube-and-rollup/)

(An index of the documentation is available at /llms.txt)