GROUPING SETS extension v16

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, or MAX
  • Be constants or functions whose return values are independent of the individual rows in the group (for example, the SYSDATE function)

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:

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 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:

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:

Output
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:

Output
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:

Output
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:

Output
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.

Output
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)