Multiple grouping clauses can be specified in a single statement using grouping sets. A grouping set can be a single element or a list of elements separated by parentheses, where an element is either a grouping-expression or a super-group. Grouping sets can combine either simple GROUP BY clauses or the more complex GROUP BY ROLLUP or CUBE clauses.

A simple GROUP BY with a single column can be considered a grouping set with one element. For example:

GROUP BY x

is the no different than

GROUP BY GROUPING SETS((x))

and

GROUP BY x,y,z

is equal to

GROUP BY GROUPING SETS((x,y,z)) 

Combining Grouping Sets

Any of the types of GROUP BY clauses can be combined. When simple grouping-expression fields are combined with other groups, they are "appended" to the beginning of the resulting grouping sets. When ROLLUP or CUBE expressions are combined, they operate like "multipliers" on the remaining expression, forming additional grouping set entries according to the definition of either ROLLUP or CUBE.

For example:

GROUP BY x, ROLLUP(y,z)

is equivalent to

GROUP BY GROUPING SETS((x,y,z)
                       (x,y)
                       (x) )

likwise,

GROUP BY x, y, ROLLUP(z,a)

is equivalent to

GROUP BY GROUPING SETS((x,y,z,a)
                       (x,y,z)
                       (x,y) )

Combining of ROLLUP elements acts as follows:

GROUP BY ROLLUP(x), ROLLUP(y,z)

is equivalent to

GROUP BY GROUPING SETS((x,y,z)
                       (x,y)
                       (x)
                       (y,z)
                       (y)
                       () )

Similarly,

GROUP BY ROLLUP(x), CUBE(y,z)

is equivalent to

GROUP BY GROUPING SETS((x,y,z)
                       (x,y)
                       (x,z)
                       (x)
                       (y,z)
                       (y)
                       (z)
                       () )

Combining of CUBE and ROLLUP elements acts as follows:

GROUP BY CUBE(x,y), ROLLUP(z,a)

is equivalent to

GROUP BY GROUPING SETS((x,y,z,a)
                       (x,y,z)
                       (x,y)
                       (x,z,a)
                       (x,z)
                       (x)
                       (y,z,a)
                       (y,z)
                       (y)
                       (z,a)
                       (z)
                       () )