SQL GROUP BY extensions like ROLLUP, CUBE, and GROUPING SETS extend the standard GROUP BY clause to generate multiple levels of aggregation in a single query. This post explains how these Oracle SQL features enable you to produce subtotals, grand totals, and cross-tabulations without writing multiple separate queries.
Rollup
- The ROLLUP extension allows you to generate subtotals
- The ROLLUP extension groups totals based on the input hierarchy
CUBE
- The CUBE extension allows you to generate subtotals
- The CUBE extension groups totals based on all combinations of the specified columns
select
nvl(region_id, 0),
nvl(sub_region_id, 0),
sum(population)
from eba_countries
group by cube(nvl(region_id, 0), nvl(sub_region_id, 0));
GROUPING_ID function
- Allows you to identify which row is a sub-total or grand total, or if the row is neither
- It will return flag values to represent subtotals, totals and grouping totals
- Only applicable in SELECT statement when used with Group by extensions
select column1, column2, grouping_id(column1, column2) as group_id,
sum(column2)
from table
group by rollup(column1, column2)
Frequently Asked Questions
What is the ROLLUP operator in SQL?
The ROLLUP operator is a GROUP BY extension that generates subtotals along a hierarchy, producing aggregated rows for each level of the hierarchy plus a grand total row, useful for generating reports with running subtotals.
What is the difference between ROLLUP and CUBE in SQL?
ROLLUP generates subtotals along one hierarchical dimension, while CUBE generates subtotals for all possible combinations of the specified columns, producing more rows but enabling cross-tabulation analysis across all dimensions.