GROUPING SETS Extension v12
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:
GROUPING SETS ( { <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 following query produces a union of groups given by columns loc, dname
, and job
.
SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY GROUPING SETS (loc, dname, job) ORDER BY 1, 2, 3;
The result is as follows:
loc | dname | job | employees ---------+------------+-----------+----------- BOSTON | | | 8 CHICAGO | | | 6 NEW YORK | | | 3 | ACCOUNTING | | 3 | OPERATIONS | | 3 | RESEARCH | | 5 | SALES | | 6 | | ANALYST | 3 | | CLERK | 5 | | MANAGER | 4 | | PRESIDENT | 1 | | SALESMAN | 4 (12 rows)
This is equivalent to the following query, which employs the use of the UNION ALL
operator.
SELECT loc AS "loc", NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY loc UNION ALL SELECT NULL, dname, NULL, COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY dname UNION ALL SELECT NULL, NULL, job, COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY job ORDER BY 1, 2, 3;
The output from the UNION ALL
query is the same as the GROUPING SETS
output.
loc | dname | job | employees ---------+------------+-----------+----------- BOSTON | | | 8 CHICAGO | | | 6 NEW YORK | | | 3 | ACCOUNTING | | 3 | OPERATIONS | | 3 | RESEARCH | | 5 | SALES | | 6 | | ANALYST | 3 | | CLERK | 5 | | MANAGER | 4 | | PRESIDENT | 1 | | SALESMAN | 4 (12 rows)
The following example shows how various types of GROUP BY
extensions can be used together within a GROUPING SETS
expression list.
SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE (job, loc)) ORDER BY 1, 2, 3;
The following is the output from this query.
loc | dname | job | employees ---------+------------+-----------+----------- BOSTON | | ANALYST | 3 BOSTON | | CLERK | 3 BOSTON | | MANAGER | 2 BOSTON | | | 8 BOSTON | | | 8 CHICAGO | | CLERK | 1 CHICAGO | | MANAGER | 1 CHICAGO | | SALESMAN | 4 CHICAGO | | | 6 CHICAGO | | | 6 NEW YORK | | CLERK | 1 NEW YORK | | MANAGER | 1 NEW YORK | | PRESIDENT | 1 NEW YORK | | | 3 NEW YORK | | | 3 | ACCOUNTING | CLERK | 1 | ACCOUNTING | MANAGER | 1 | ACCOUNTING | PRESIDENT | 1 | ACCOUNTING | | 3 | OPERATIONS | ANALYST | 1 | OPERATIONS | CLERK | 1 | OPERATIONS | MANAGER | 1 | OPERATIONS | | 3 | RESEARCH | ANALYST | 2 | RESEARCH | CLERK | 2 | RESEARCH | MANAGER | 1 | RESEARCH | | 5 | SALES | CLERK | 1 | SALES | MANAGER | 1 | SALES | SALESMAN | 4 | SALES | | 6 | | ANALYST | 3 | | CLERK | 5 | | MANAGER | 4 | | PRESIDENT | 1 | | SALESMAN | 4 | | | 17 | | | 17 (38 rows)
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.
SELECT loc, NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY loc ORDER BY 1;
The following is the result set from the GROUP BY loc
clause.
loc | dname | job | employees ----------+-------+-----+----------- BOSTON | | | 8 CHICAGO | | | 6 NEW YORK | | | 3 (3 rows)
The following query uses the GROUP BY ROLLUP (dname, job)
clause.
SELECT NULL AS "loc", dname, job, COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY ROLLUP (dname, job) ORDER BY 2, 3;
The following is the result set from the GROUP BY ROLLUP (dname, job)
clause.
loc | dname | job | employees ----+------------+-----------+----------- | ACCOUNTING | CLERK | 1 | ACCOUNTING | MANAGER | 1 | ACCOUNTING | PRESIDENT | 1 | ACCOUNTING | | 3 | OPERATIONS | ANALYST | 1 | OPERATIONS | CLERK | 1 | OPERATIONS | MANAGER | 1 | OPERATIONS | | 3 | RESEARCH | ANALYST | 2 | RESEARCH | CLERK | 2 | RESEARCH | MANAGER | 1 | RESEARCH | | 5 | SALES | CLERK | 1 | SALES | MANAGER | 1 | SALES | SALESMAN | 4 | SALES | | 6 | | | 17 (17 rows)
The following query uses the GROUP BY CUBE (job, loc)
clause.
SELECT loc, NULL AS "dname", job, COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY CUBE (job, loc) ORDER BY 1, 3;
The following is the result set from the GROUP BY CUBE (job, loc)
clause.
loc | dname | job | employees ---------+-------+-----------+----------- BOSTON | | ANALYST | 3 BOSTON | | CLERK | 3 BOSTON | | MANAGER | 2 BOSTON | | | 8 CHICAGO | | CLERK | 1 CHICAGO | | MANAGER | 1 CHICAGO | | SALESMAN | 4 CHICAGO | | | 6 NEW YORK | | CLERK | 1 NEW YORK | | MANAGER | 1 NEW YORK | | PRESIDENT | 1 NEW YORK | | | 3 | | ANALYST | 3 | | CLERK | 5 | | MANAGER | 4 | | PRESIDENT | 1 | | SALESMAN | 4 | | | 17 (18 rows)
If the previous three queries are combined with the UNION ALL
operator, a concatenation of the three results sets is produced.
SELECT loc AS "loc", NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY loc UNION ALL SELECT NULL, dname, job, count(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY ROLLUP (dname, job) UNION ALL SELECT loc, NULL, job, count(*) AS "employees" FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY CUBE (job, loc) ORDER BY 1, 2, 3;
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.
loc | dname | job | employees ---------+------------+-----------+----------- BOSTON | | ANALYST | 3 BOSTON | | CLERK | 3 BOSTON | | MANAGER | 2 BOSTON | | | 8 BOSTON | | | 8 CHICAGO | | CLERK | 1 CHICAGO | | MANAGER | 1 CHICAGO | | SALESMAN | 4 CHICAGO | | | 6 CHICAGO | | | 6 NEW YORK | | CLERK | 1 NEW YORK | | MANAGER | 1 NEW YORK | | PRESIDENT | 1 NEW YORK | | | 3 NEW YORK | | | 3 | ACCOUNTING | CLERK | 1 | ACCOUNTING | MANAGER | 1 | ACCOUNTING | PRESIDENT | 1 | ACCOUNTING | | 3 | OPERATIONS | ANALYST | 1 | OPERATIONS | CLERK | 1 | OPERATIONS | MANAGER | 1 | OPERATIONS | | 3 | RESEARCH | ANALYST | 2 | RESEARCH | CLERK | 2 | RESEARCH | MANAGER | 1 | RESEARCH | | 5 | SALES | CLERK | 1 | SALES | MANAGER | 1 | SALES | SALESMAN | 4 | SALES | | 6 | | ANALYST | 3 | | CLERK | 5 | | MANAGER | 4 | | PRESIDENT | 1 | | SALESMAN | 4 | | | 17 | | | 17 (38 rows)