AntDB-Oracle Compatibility Developer's Manual P2–5
Published: 2023-08-29 06:54:30

##### GROUPING SETS extension

Using the GROUPING SETS extension in the GROUP BY clause provides a way to generate a result set that is actually a concatenation of multiple result sets based on different groupings. In other words, performing the UNION ALL operation combines the result sets of multiple groupings into a single result set.

Note that this is a UNION ALL operation, so the GROUPING SETS extension does not eliminate duplicate rows from the grouped result sets.

grouping sets is to do grouping for each parameter in the argument, that is, there are several parameters to do grouping several times, for example, using group by grouping sets(A,B,C), then group by for (A), (B), (C), if you use group by grouping sets ((A,B),C), then group by for (A,B), (C).

The syntax of a single GROUPING SETS extension is as follows.

GROUPING SETS (

{ *expr\_1* \| ( *expr\_1a* \[, *expr\_1b* ] ...) \|

ROLLUP ( *expr\_list* ) \| CUBE ( *expr\_list* )

} \[, ...] )

The GROUPING SETS extension can contain one or more comma-separated expressions, a list of expressions enclosed in parentheses, any combination of the ROLLUP extension and the CUBE extension.

The GROUPING SETS extension is specified in the context of the GROUP BY clause, as follows.

SELECT select\_list FROM ...

GROUP BY \[... ,] GROUPING SETS ( *expression\_list* ) \[, ...]

The items specified in select\_list must also appear in GROUPING SETS expression\_list. Either 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 (e.g., the SYSDATE function).

If you want the output to be displayed in a meaningful structure, you should use the ORDER BY clause. If the ORDER BY clause is not specified, the order of the result set is not guaranteed.

The following query generates a concatenation of the groups given by the 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 
----------+------------+-----------+-----------
 CHICAGO  |            |           |         6
 DALLAS   |            |           |         5
 NEW YORK |            |           |         3
          | ACCOUNTING |           |         3
          | RESEARCH   |           |         5
          | SALES      |           |         6
          |            | ANALYST   |         2
          |            | CLERK     |         4
          |            | MANAGER   |         3
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
(11 rows)
```

This is equivalent to the following query, which uses the UNION ALL operator.

```
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 following example shows how to use the various types of GROUP BY extensions together in a list of GROUPING SETS expressions.

```
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 output is a concatenation of result sets that will be generated from GROUP BY loc, GROUP BY ROLLUP (dname, job), and GROUP BY CUBE (job, loc), respectively.

```
   LOC    |   DNAME    |    JOB      | employees 
----------+------------+-------------+------------
 CHICAGO  |            | CLERK       |         1
 CHICAGO  |            | MANAGER     |         1
 CHICAGO  |            | SALESMAN    |         4
 CHICAGO  |            |             |         6
 CHICAGO  |            |             |         6
 DALLAS   |            | ANALYST     |         2
 DALLAS   |            | CLERK       |         2
 DALLAS   |            | MANAGER     |         1
 DALLAS   |            |             |         5
 DALLAS   |            |             |         5
 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
          | RESEARCH   | ANALYST     |         2
          | RESEARCH   | CLERK       |         2
          | RESEARCH   | MANAGER     |         1
          | RESEARCH   |             |         5
          | SALES      | CLERK       |         1
          | SALES      | MANAGER     |         1
          | SALES      | SALESMAN    |         4
          | SALES      |             |         6
          |            | ANALYST     |         2
          |            | CLERK       |         4
          |            | MANAGER     |         3
          |            | PRESIDENT   |         1
          |            | SALESMAN    |         4
          |            |             |        14
          |            |             |        14
(34 rows)
```