Export (0) Print
Expand All
6 out of 8 rated this helpful - Rate this topic

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

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 floating-point numbers might return slightly different results.

The following statements are equivalent:

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))
SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year

The following clauses return the same grand totals:

GROUP BY GROUPING SETS ( () )
GROUP BY ()

The following clauses return the same single sets:

GROUP BY GROUPING SETS ( (C1, C2, ..., Cn) )
GROUP BY C1, C2, ..., Cn

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:

GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)
GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn)
    ,(C1, C2, ..., Cn-1)
    ...
    ,(C1, C2)
    ,(C1)
    ,() )

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:

GROUP BY CUBE (C1, C2, C3, ..., Cn-2, Cn-1, Cn)

GROUP BY GROUPING SETS (
     (C1, C2, C3, ..., Cn-2, Cn-1, Cn) -- All dimensions are included.
    ,( , C2, C3, ..., Cn-2, Cn-1, Cn) -- n-1 dimensions are included.
    ,(C1, C3, ..., Cn-2, Cn-1, Cn)
    …
    ,(C1, C2, C3, ..., Cn-2, Cn-1,)
    ,(C3, ..., Cn-2, Cn-1, Cn) -- n-2 dimensions included
    ,(C1  ..., Cn-2, Cn-1, Cn)
    …
    ,(C1, C2) -- 2 dimensions are included.
    ,…
    ,(C1, Cn)
    ,…
    ,(Cn-1, Cn)
    ,…
    ,(C1) -- 1 dimension included
    ,(C2)
    ,…
    ,(Cn-1)
    ,(Cn)
    ,() ) -- Grand total, 0 dimension is included.

The following clauses are equivalent:

GROUP BY CUBE (C1, C2, C3)
GROUP BY GROUPING SETS ( (C1, C2, C3)
    ,(C1, C2)
    ,(C1, C3)
    ,(C2, C3)
    ,(C1)
    ,(C2)
    ,(C3)
    ,() )

The following clauses are equivalent:

ROLLUP(A, (C1, C2, ..., Cn) )
ROLLUP( (A), (C1, C2, ..., Cn) )
GROUPING SETS ( (A, C1, C2, ..., Cn), (A), () )

The following clauses are equivalent:

CUBE(A, (C1, C2, ..., Cn) )
CUBE( (A), (C1, C2, ..., Cn) )
GROUPING SETS ( (), (A), (C1, C2, ..., Cn), (A, C1, C2, ..., Cn) )

The following clauses are equivalent:

GROUP BY A, CUBE (B, C)
GROUP BY GROUPING SETS ( (A), (A, B), (A, C), (A, B, C ))

The following clauses are equivalent:

GROUP BY A, GROUPING SETS ( (B), (C) )
GROUP BY GROUPING SETS ( (A, B), (A, C) )

The following clauses are equivalent:

GROUP BY ROLLUP (A, B), ROLLUP(C, D)
GROUP BY GROUPING SETS
    ( (),(C),(C,D),(A),(A,C),(A,C,D),(A,B),(A,B,C),(A,B,C,D) )

The following clauses are equivalent:

GROUP BY GROUPING SETS ( (A), ROLLUP (B, C) )
GROUP BY GROUPING SETS ( (A), (B,C), (B), () )

The following clauses are equivalent:

GROUP BY GROUPING SETS(A, (B, ROLLUP(C, D)) )
GROUP BY GROUPING SETS (A, B, (B,C), (B, C, D) () )
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.