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)