Grouping sets

The GROUPING SETS () syntax offers a generalization of the rollup and cube capability. Any rollup() or cube() operation can be translated into a grouping sets specification. The syntax is as follows:

GROUP BY [<set quantifier>] GROUPING SETS(<grouping-set-list>);

Where <set quantifier> is either DISTINCT or ALL and defaults to ALL, and <grouping-set-list> is a comma-separated list of grouping sets, each of which is a parenthesized <grouping-col-list>, as in the following example:

SELECT col1, col2, col3, COUNT(*) FROM <table> GROUP BY GROUPING SETS 
((col1,col2), (col2,col3), (col2), ());

This example is equivalent to grouping by the following groupings simultaneously:

GROUP BY (col1, col2)
GROUP BY (col2, col3)
GROUP BY col2
GROUP BY ()

The result of the previous SELECT is equivalent to the result of doing a UNION ALL for the four selects aggregating at the four group levels. So a rollup() or cube() operation can be translated into a grouping sets specification.

An entry in a <grouping-set-list> can itself be a <grouping-set-list>, and so can also be a cube() or rollup(). Any such complex specified grouping-set-list can always be expanded into a grouping-set-list, as in the following example:

GROUPING SETS (ROLLUP(col1,col2), CUBE(col1,col2))

Which is equivalent to the following:

GROUPING SETS ((col1,col2), (col1), (), (col1,col2), (col1), (col2), 
())

With the following distinct set quantifier:

DISTINCT GROUPING SETS (ROLLUP(col1,col2), CUBE(col1,col2))

Which is equivalent to the following, showing that CUBE(<list>) is a superset of ROLLUP(<list>):

GROUPING SETS ((col1,col2), (col1), (col2), ())

Also, the grouping set (col1, col2) is equivalent to (col2, col1), so the elimination of duplicates in the grouping-set-list is done by putting list entries into a canonical form that lists unique entries in the order in which they occur in the grouping sets clause.

Multiple grouping sets at the same level (not nested) are handled as in the following example:

GROUP BY GROUPING SETS ((A), (B)), GROUPING SETS ((X, Y), (Z))

Which is equivalent to the following:

GROUP BY GROUPING SETS ((A, X, Y), (A, Z), (B, X, Y), (B, Z))

Another example is the following:

GROUP BY A, GROUPING SETS ((X,Y), (Z))

Which is equivalent to the following:

GROUP BY GROUPING SETS ((A, X, Y), (A, Z))

Using the GROUPING function helps to distinguish differences. The following is an example of a rollup query:

STATE  |     CITY      |  SUM
_____________________________
 CA    | Los Angeles   |  600
 CA    | San Diego     |  225
 CA    | San Francisco |  450
 CA    |               | 1275
 MA    | Boston        |  460
 MA    | Springfield   |  345
 MA    |               |  805
       |               | 2080

Since some group column values are NULL in superaggregate rows, if there was a NULL city, it might be difficult to tell an aggregate row from a superaggregate. By using GROUPING (city), the result would return a 0 if the city is included in the “group by,” and would return a 1 if the city is not included in the “group by.” The following example shows the result:

STATE  |     CITY      |  SUM  | GROUPING(city) 
_____________________________________________
 CA    | Los Angeles   |  600  |       0       
 CA    | San Diego     |  225  |       0       
 CA    | San Francisco |  450  |       0       
 CA    |               | 1275  |       1       
 MA    | Boston        |  460  |       0       
 MA    | Springfield   |  345  |       0       
 MA    |               |  805  |       1       
       |               | 2080  |       1

By using GROUPING (state), the result would return a 0 if the state is included in the “group by,” and would return a 1 if the state is not included in the “group by.” The following example shows the result:

STATE  |     CITY      |  SUM  | GROUPING(city)  | GROUPING(state) 
________________________________________________________________
 CA    | Los Angeles   |  600  |       0         |       0
 CA    | San Diego     |  225  |       0         |       0 
 CA    | San Francisco |  450  |       0         |       0 
 CA    |               | 1275  |       1         |       0 
 MA    | Boston        |  460  |       0         |       0 
 MA    | Springfield   |  345  |       0         |       0 
 MA    |               |  805  |       1         |       0 
       |               | 2080  |       1         |       1