GROUPING function v16

When using the ROLLUP, CUBE, or GROUPING SETS extensions to the GROUP BY clause, it can sometimes be difficult to differentiate between the various levels of subtotals generated by the extensions as well as the base aggregate rows in the result set. The GROUPING function provides a means of making this distinction.

Syntax

The general syntax for use of the GROUPING function is:

SELECT [ <expr> ...,] GROUPING( <col_expr> ) [, <expr> ] ...
FROM ...
GROUP BY [...,]
  { ROLLUP | CUBE | GROUPING SETS }( [...,] <col_expr>
  [, ...] ) [, ...]

The GROUPING function takes a single parameter that must be an expression of a dimension column specified in the expression list of a ROLLUP, CUBE, or GROUPING SETS extension of the GROUP BY clause.

The return value of the GROUPING function is either a 0 or 1. In the result set of a query:

  • If the column expression specified in the GROUPING function is null because the row represents a subtotal over multiple values of that column, then the GROUPING function returns a value of 1.
  • If the row returns results based on a particular value of the column specified in the GROUPING function, then the GROUPING function returns a value of 0.

In the latter case, the column can be null as well as non-null. In any case, it's for a particular value of that column, not a subtotal across multiple values.

Examples

The following query shows how the return values of the GROUPING function correspond to the subtotal lines:

SELECT loc, dname, job, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc",
  GROUPING(dname) AS "gf_dname",
  GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
ORDER BY 1, 2, 3;

In the three right-most columns displaying the output of the GROUPING functions, a value of 1 appears on a subtotal line wherever a subtotal is taken across values of the corresponding columns:

Output
loc      |   dname    |    job    | employees | gf_loc | gf_dname | gf_job
---------+------------+-----------+-----------+--------+----------+--------
BOSTON   | OPERATIONS | ANALYST   |         1 |      0 |        0 |      0
BOSTON   | OPERATIONS | CLERK     |         1 |      0 |        0 |      0
BOSTON   | OPERATIONS | MANAGER   |         1 |      0 |        0 |      0
BOSTON   | OPERATIONS |           |         3 |      0 |        0 |      1
BOSTON   | RESEARCH   | ANALYST   |         2 |      0 |        0 |      0
BOSTON   | RESEARCH   | CLERK     |         2 |      0 |        0 |      0
BOSTON   | RESEARCH   | MANAGER   |         1 |      0 |        0 |      0
BOSTON   | RESEARCH   |           |         5 |      0 |        0 |      1
BOSTON   |            |           |         8 |      0 |        1 |      1
CHICAGO  | SALES      | CLERK     |         1 |      0 |        0 |      0
CHICAGO  | SALES      | MANAGER   |         1 |      0 |        0 |      0
CHICAGO  | SALES      | SALESMAN  |         4 |      0 |        0 |      0
CHICAGO  | SALES      |           |         6 |      0 |        0 |      1
CHICAGO  |            |           |         6 |      0 |        1 |      1
NEW YORK | ACCOUNTING | CLERK     |         1 |      0 |        0 |      0
NEW YORK | ACCOUNTING | MANAGER   |         1 |      0 |        0 |      0
NEW YORK | ACCOUNTING | PRESIDENT |         1 |      0 |        0 |      0
NEW YORK | ACCOUNTING |           |         3 |      0 |        0 |      1
NEW YORK |            |           |         3 |      0 |        1 |      1
         |            |           |        17 |      1 |        1 |      1
(20 rows)

You can use these indicators as screening criteria for particular subtotals. For example, using the previous query, you can display only those subtotals for loc and dname combinations by using the GROUPING function in a HAVING clause:

SELECT loc, dname, job, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc",
  GROUPING(dname) AS "gf_dname",
  GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
HAVING GROUPING(loc) = 0
  AND  GROUPING(dname) = 0
  AND  GROUPING(job) = 1
ORDER BY 1, 2;

This query produces the following result:

Output
loc      |   dname    | job | employees | gf_loc | gf_dname | gf_job
---------+------------+-----+-----------+--------+----------+--------
BOSTON   | OPERATIONS |     |         3 |      0 |        0 |      1
BOSTON   | RESEARCH   |     |         5 |      0 |        0 |      1
CHICAGO  | SALES      |     |         6 |      0 |        0 |      1
NEW YORK | ACCOUNTING |     |         3 |      0 |        0 |      1
(4 rows)

You can use the GROUPING function to distinguish a subtotal row from a base aggregate row or from certain subtotal rows. These subtotal rows have one item in the expression list that returns null as a result of the column on which the expression is based being null for one or more rows in the table. This is opposed to representing a subtotal over the column.

To illustrate this point, the following row is added to the emp table, which provides a row with a null value for the job column:

INSERT INTO emp (empno,ename,deptno) VALUES (9004,'PETERS',40);

The following query is issued using a reduced number of rows for clarity:

SELECT loc, job, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc",
  GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno AND loc = 'BOSTON'
GROUP BY CUBE (loc, job)
ORDER BY 1, 2;

The output contains two rows containing BOSTON in the loc column and spaces in the job column (fourth and fifth entries in the table):

Output
loc    |   job   | employees | gf_loc | gf_job
-------+---------+-----------+--------+--------
BOSTON | ANALYST |         3 |      0 |      0
BOSTON | CLERK   |         3 |      0 |      0
BOSTON | MANAGER |         2 |      0 |      0
BOSTON |         |         1 |      0 |      0
BOSTON |         |         9 |      0 |      1
       | ANALYST |         3 |      1 |      0
       | CLERK   |         3 |      1 |      0
       | MANAGER |         2 |      1 |      0
       |         |         1 |      1 |      0
       |         |         9 |      1 |      1
(10 rows)

The fifth row, where the GROUPING function on the job column (gf_job) returns 1, indicates this is a subtotal over all jobs. The row contains a subtotal value of 9 in the employees column.

In the fourth row, the GROUPING function on the job column as well as on the loc column returns 0. This indicates that this is a base aggregate of all rows, where loc is BOSTON and job is null, which is the row inserted for this example. The employees column contains 1, which is the count of the single such row inserted.

In the ninth row (next to last) the GROUPING function on the job column returns 0, while the GROUPING function on the loc column returns 1. This indicates that this is a subtotal over all locations where the job column is null which, again, is a count of the single row inserted for this example.