When you use the ROLLUP, CUBE, or GROUPING SETS extensions to the GROUP BY clause, the various levels of subtotals generated by the extensions may not be distinguished from the base aggregate rows in the result set. The GROUPING function allows you to distinguish them.

The GROUPING function has the following general syntax:

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

The GROUPING function uses 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 value returned by the GROUPING function is either 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, 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, the GROUPING function returns a value of 0. In the latter case, the column can be a null or non-null values. In both cases, it is for a particular value of that column, not a subtotal across multiple values.

The following query shows how the values returned by the GROUPING function correspond to the subtotal rows.

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 returned by the GROUPING function, a value of 1 appears on a subtotal row wherever a subtotal is taken across values of the corresponding columns.

   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)

These indicators can be used as the criteria to filter particular subtotals. For example, in the previous query, you can display only those subtotals for the combinations of loc and dname 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;

The following example shows the result of this query:

   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)

The GROUPING function can be used to distinguish a subtotal row from a base aggregate row or from certain subtotal rows. In these rows, one of the items in the expression list returns null due to the null column on which the expression is based. The null column corresponds to one or more rows in the table. The item does not represent a subtotal over the column.

For example, add the following row to the emp table. As a result, a row with a null value is created for the job column.

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

In the following query, the number of rows is reduced 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;

In the following output, two rows contains BOSTON in the loc column and spaces in the job column. The fourth and fifth entries in the table show these two rows.

  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 GROUPING function on the job column (gf_job) returns 1 in the fifth row to indicate that this value is a subtotal over all jobs. The row contains a subtotal value of 9 in the employees column.

The GROUPING function on the job column and on the loc column returns 0 in the fourth row to indicate that this value is a base aggregate of all rows where loc is BOSTON and job is null. The fourth row is inserted for this example. The employees column contains 1, which indicates the number of null job rows.

In the ninth row next to the last row, the GROUPING function on the job column returns 0 and the GROUPING function on the loc column returns 1. These values are a subtotal over all locations where the job column is null. The employees column indicates the number of null job rows.