GROUPING SETS Equivalents
A GROUP BY clause that uses GROUPING SETS can generate a result set equvalent to that generated by a UNION ALL of multiple simple GROUP BY clauses. GROUPING SETS can generate a result equivalent to that generated by a simple GROUP BY, ROLLUP or CUBE operation. Different combinations of GROUPING SETS, ROLLUP, or CUBE can generate equivalent result sets.
This topic provides examples of GROUPING SETS equivalents. The following abbreviations are used in the examples:
Agg(): any aggregate function
(arg): an argument
GROUPING SETS Equivalent of UNION ALL
Specifying GROUPING SETS (<grouping set> [,...n ]) as the GROUP BY list is equivalent to a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. Aggregates on floatingpoint numbers might return slightly different results.
The following statements are equivalent:


GROUPING SETS Equivalents of a Simple GROUP BY
The following clauses return the same grand totals:


The following clauses return the same single sets:


GROUPING SETS ROLLUP Equivalents
GROUP BY ROLLUP (<composite element list>) with n dimensions in the input list is equivalent to GROUPING SETS, with all prefixes (n+1) of its input list as its GROUPING SETS.
The following clauses are equivalent:


GROUPING SETS CUBE Equivalents
GROUP BY CUBE (<composite element list>) with n dimensions in the input list is equivalent to GROUPING SETS with the full set (2n combinations of the dimensions in input list) of its input list as its GROUPING SETS.
The following clauses are equivalent:


The following clauses are equivalent:


Composite Columns in ROLLUP Including a Grouping Set Inside a ROLLUP
The following clauses are equivalent:



Composite Columns in CUBE Including a Grouping Set Inside a CUBE
The following clauses are equivalent:



GROUP BY That Contains GROUPING SETS, ROLLUP, or CUBE
The following clauses are equivalent:


The following clauses are equivalent:


The following clauses are equivalent:


ROLLUP Included in a GROUPING SETS List
The following clauses are equivalent:


ROLLUP Included Inside a Grouping Set
The following clauses are equivalent:

