A GROUPING SETS extension within the GROUP BY clause is used to generate one result set that is the concatenation of multiple results sets based on different groupings. The UNION ALL operator is used to combine the result sets of multiple groupings into one result set.
The UNION ALL operator and the GROUPING SETS extension do not remove duplicate rows from the combined result sets.
A single GROUPING SETS extension has the following syntax:
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 parentheses, ROLLUP extensions, and CUBE extensions.
The GROUPING SETS extension is specified within the context of the GROUP BY clause. The following example shows this extension:
SELECT select_list FROM ...
GROUP BY [... ,] GROUPING SETS ( expression_list ) [, ...]
The items specified in select_list must appear in the GROUPING SETS expression_list, be aggregate functions such as COUNT, SUM, AVG, MIN, or MAX, or be constants or functions such as the SYSDATE function whose returned values are independent of the individual rows in the group.
The GROUP BY clause may specify multiple GROUPING SETS extensions and multiple occurrences of other GROUP BY extensions and individual expressions.
You must use the ORDER BY clause if you want to display the output in a meaningful structure. The order of the result set is not determined if no ORDER BY clause is specified.
The following query generates a union of groups specified 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 following example shows the result of this query:
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)
To retrieve the same result, you can also use the UNION ALL operator in the following query:
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 example shows the output from the preceding 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 a concatenation of the result sets of GROUP BY loc, GROUP BY ROLLUP (dname, job), and GROUP BY CUBE (job, loc). The following example shows these queries:
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 example shows the result set of 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 query is the result set of 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 example shows the result set of 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 you combine the preceding three queries by using the UNION ALL operator, a concatenation of the three results sets is generated.
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 example shows the same output 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)