Lets one GROUP BY query return results for several different groupings at once, giving the same output as stitching individual aggregations together with UNION ALL. CUBE and ROLLUP are convenient shorthands built on it.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains GROUPING SETS in context, with comparison tables and the common traps.
Terms in this definition
- GROUP BY
Collapses rows sharing the same values of chosen expressions into one row per bucket, with aggregate functions evaluated for each bucket. Writing
GROUP BY ALLpicks every expression in the select list that isn't an aggregate. - RETURN
Comes after the VAR definitions in DAX and holds the expression whose value the formula gives back. While debugging, pointing it at one variable for a while is a useful trick.
- Aggregations
Summary queries over a large DirectQuery table can be answered from memory instead of the source thanks to this semantic model feature: it keeps a concealed summary table cached and sends qualifying queries there, while anything needing fine detail still goes back to the source.
- ALL
A DAX function that ignores any filters and gives back every row of a table or every value of the named columns. Used within CALCULATE, it works as a modifier that clears filters, although REMOVEFILTERS states that intent more clearly where it is available.
- CUBE
A Spark SQL
GROUP BYextension that returns totals for each possible combination of the named columns, which is 2^n grouping sets once the overall total is counted.ROLLUP, by contrast, returns only the hierarchy-based subset. - ROLLUP
An option for GROUP BY, available in both Spark SQL and T-SQL, that produces subtotals at each level of a hierarchy plus an overall total; for example ROLLUP(a, b) groups by (a, b), then by (a), then by nothing. CUBE goes further and covers every combination of the columns.