GROUPING SETS clause
The GROUPING SETS clause in GROUP BY allows us to specify more than one GROUP BY options in the same record set. All GROUPING clause query can be logically expressed in terms of several GROUP BY queries connected by UNION. Table-1 shows several such equivalent statements. This is helpful in forming the idea of the GROUPING SETS clause. A blank set ( ) in the GROUPING SETS clause calculates the overall aggregate.
Table 1 - GROUPING SET queries and the equivalent GROUP BY queries
Aggregate Query with GROUPING SETS |
Equivalent Aggregate Query with GROUP BY |
|---|---|
SELECT a, b, SUM(c) FROM tab1 GROUP BY GROUPING SETS ( (a,b) ) |
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a,b), a) |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b |
SELECT a,b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS (a,b) |
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), a, b, ( ) ) |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION |
|
|