Rollup

1. Supports proprietary GROUP BY … WITH ROLLUP syntax

Group by rollup (…) with N column lists creates N+1 grouping sets by “dropping columns from the right, one by one”.0 The empty list () is the last element (thus N+1 grouping sets).

In other words, the following two group by clauses are equivalent:

GROUP BY ROLLUP( a, b, c )

GROUP BY GROUPING SETS ( (a, b, c)
                       , (a, b   )
                       , (a      )
                       , (       )
                       )

Note that I wrote “column lists”. Similar to the grouping sets specification, rollup (and cube) accept a list of column lists. The following rollup specification has only one argument—a single column list consisting of two columns kept together by a pair of parenthesis.

GROUP BY ROLLUP ( (a, b) )

The equivalent grouping sets specification has two elements (N+1): the column list from the rollup specification and the empty grouping set.

GROUP BY GROUPING SETS ( (a, b)
                       , (    )
                       )