2.2.6.3 GROUPING SETS Extension

Table of Contents Previous Next



The use of the GROUPING SETS extension within the GROUP BY clause provides a means to produce one result set that is actually the concatenation of multiple results sets based upon different groupings. In other words, a UNION ALL operation is performed combining the result sets of multiple groupings into one result set.
Note that a UNION ALL operation, and therefore the GROUPING SETS extension, do not eliminate duplicate rows from the result sets that are being combined together.
The syntax for a single GROUPING SETS extension is as follows:
{ expr_1 | ( expr_1a [, expr_1b ] ...) |
ROLLUP ( expr_list ) | CUBE ( expr_list )
A GROUPING SETS extension can contain any combination of one or more comma-separated expressions, lists of expressions enclosed within parenthesis, ROLLUP extensions, and CUBE extensions.
The GROUPING SETS extension is specified within the context of the GROUP BY clause as shown by the following:
SELECT select_list FROM ...
GROUP BY [... ,] GROUPING SETS ( expression_list ) [, ...]
The items specified in select_list must also appear in the GROUPING SETS expression_list; or they must be aggregate functions such as COUNT, SUM, AVG, MIN, or MAX; or they must be constants or functions whose return values are independent of the individual rows in the group (for example, the SYSDATE function).
The GROUP BY clause may specify multiple GROUPING SETS extensions as well as multiple occurrences of other GROUP BY extensions and individual expressions.
The ORDER BY clause should be used if you want the output to display in a meaningful structure. There is no guarantee on the order of the result set if no ORDER BY clause is specified.
The output from the UNION ALL query is the same as the GROUPING SETS output.
The following example shows how various types of GROUP BY extensions can be used together within a GROUPING SETS expression list.
The output is basically a concatenation of the result sets that would be produced individually from GROUP BY loc, GROUP BY ROLLUP (dname, job), and GROUP BY CUBE (job, loc). These individual queries are shown by the following.
The following query uses the GROUP BY ROLLUP (dname, job) clause.
The following is the result set from the GROUP BY ROLLUP (dname, job) clause.
The following query uses the GROUP BY CUBE (job, loc) clause.
The following is the result set from the GROUP BY CUBE (job, loc) clause.
If the previous three queries are combined with the UNION ALL operator, a concatenation of the three results sets is produced.
The following is the output, which is the same as when the GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE (job, loc)) clause is used.


Table of Contents Previous Next