GROUPING SETS extension v17
The use of the GROUPING SETS extension in the GROUP BY clause provides a means to produce one result set that's the concatenation of multiple results sets based on different groupings. In other words, a UNION ALL operation is performed combining the result sets of multiple groupings into one result set.
A UNION ALL operation, and therefore the GROUPING SETS extension, doesn't eliminate duplicate rows from the result sets that are being combined.
Syntax
The syntax for a single GROUPING SETS extension is:
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 in parentheses, ROLLUP extensions, and CUBE extensions.
The GROUPING SETS extension is specified in the context of the GROUP BY clause:
SELECT <select_list> FROM ... GROUP BY [... ,] GROUPING SETS ( <expression_list> ) [, ...]
The items specified in select_list must also either:
- Appear in the
GROUPING SETS expression_list - Be aggregate functions such as
COUNT, SUM, AVG, MIN, orMAX - Be constants or functions whose return values are independent of the individual rows in the group (for example, the
SYSDATEfunction)
Use the GROUP BY clause to specify multiple GROUPING SETS extensions as well as multiple occurrences of other GROUP BY extensions and individual expressions.
Use the ORDER BY clause to display the output in a meaningful structure and to guarantee the order of the result set.
Examples
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 uses 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)This example shows how you can use various types of GROUP BY extensions together in 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 from GROUP BY loc, GROUP BY ROLLUP (dname, job), and GROUP BY CUBE (job, loc). These individual queries are:
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 result set from the GROUP BY loc clause is:
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 result set from the GROUP BY ROLLUP (dname, job) clause is:
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 result set from the GROUP BY CUBE (job, loc) clause is:
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 output 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)