GROUPING SETS, ROLLUP and CUBE - e6data Docs

GROUP BY clause

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

GROUP BY GROUPING SETS (<grouping set> [, ...])

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.

GROUP BY ROLLUP (<column> [, ...])

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.

GROUP BY CUBE (<column> [, ...])

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

Last modified on October 16, 2026