> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# GROUPING SETS, ROLLUP and CUBE

> Compute several levels of aggregation, such as subtotals and grand totals, in one GROUP BY.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">GROUP BY clause</span></div>

`GROUPING SETS`, `ROLLUP` and `CUBE` extend `GROUP BY` so one query returns rows grouped at several levels. Columns that aren't part of a row's grouping come back as `NULL`.

| Clause | Groups by |
| - | - |
| `GROUPING SETS (a, b)` | Each listed set separately: `a`, then `b` |
| `ROLLUP (a, b)` | `(a, b)`, then `(a)`, then the grand total |
| `CUBE (a, b)` | Every combination: `(a, b)`, `(a)`, `(b)` and the grand total |

## GROUPING SETS

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
GROUP BY GROUPING SETS (<grouping set> [, ...])
```

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT count(cc_company) AS total_count, cc_county, cc_class
FROM call_center
WHERE cc_county IN ('Williamson County', 'Bronx County', 'Huron County')
GROUP BY GROUPING SETS (cc_county, cc_class);
```

| total\_count | cc\_county | cc\_class |
| - | - | - |
| 2 | Williamson County | NULL |
| 3 | Bronx County | NULL |
| 1 | Huron County | NULL |
| 2 | NULL | large |
| 4 | NULL | medium |

## ROLLUP

Returns the grouped rows plus subtotal rows for each level, ending with a grand total.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
GROUP BY ROLLUP (<column> [, ...])
```

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT count(cc_company) AS total_count, cc_county, cc_class
FROM call_center
WHERE cc_county IN ('Williamson County', 'Bronx County', 'Huron County')
GROUP BY ROLLUP (cc_county, cc_class);
```

| total\_count | cc\_county | cc\_class |
| - | - | - |
| 2 | Williamson County | NULL |
| 3 | Bronx County | NULL |
| 1 | Huron County | NULL |
| 2 | Williamson County | large |
| 3 | Bronx County | medium |
| 1 | Huron County | medium |
| 6 | NULL | NULL |

## CUBE

Returns everything `ROLLUP` returns, plus subtotals for every other combination of the columns.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
GROUP BY CUBE (<column> [, ...])
```

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT count(cc_company) AS total_count, cc_county, cc_class
FROM call_center
WHERE cc_county IN ('Williamson County', 'Bronx County', 'Huron County')
GROUP BY CUBE (cc_county, cc_class);
```

| total\_count | cc\_county | cc\_class |
| - | - | - |
| 6 | NULL | NULL |
| 2 | Williamson County | NULL |
| 4 | Bronx County | NULL |
| 2 | Huron County | NULL |
| 3 | Williamson County | large |
| 1 | Bronx County | medium |
| 2 | Huron County | medium |
| 3 | NULL | large |
| 1 | NULL | medium |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.