GROUPING

The GROUPING function enables you to determine whether a NULL is a stored NULL or an indication of a subtotal or grand total. Using a single column as its argument, GROUPING returns a 1 when it encounters a null value created by a ROLLUP or CUBE operation, indicating a subtotal or grand total. Any other type of value, including a stored NULL, returns a 0.

Note:

See "GROUP BY Clause" for details on ROLLUP and CUBE clauses.

Syntax

The GROUPING function is applicable only in a SELECT statement that contains a GROUP BY clause. It can be used in the select list and HAVING clause of the SELECT query that includes the GROUP BY clause. The expression indicated in the GROUPING function syntax must match one of the expressions contained in the GROUP BY clause.

The following syntax uses GROUPING to identify the results from the expression listed as an aggregate or not:

SELECT ...  [GROUPING(Expression) ... ]  ... 
  GROUP BY ... { RollupCubeClause | GroupingSetsClause } ...

The following syntax uses GROUPING within a HAVING clause to identify the results from the expression listed as an aggregate or not:

SELECT ...
  GROUP BY ... { RollupCubeClause | GroupingSetsClause } ...
 HAVING GROUPING(Expression) = 1

Parameters

Parameter	Description
Expression	Valid expression syntax. See Expressions.
RollupCubeClause	The GROUP BY clause may include one or more ROLLUP or CUBE clauses. See "GROUP BY Clause" for full details.
GroupingSetsClause	The GROUP BY clause may include one or more GROUPING SETS clauses. The GROUPING SETS clause enables you to explicitly specify which groupings of data that the database returns.

Examples

The following example shows how the grouping function returns a '1' when it encounters the grand total for the department.

Command> columnlabels on;

Command> SELECT department_id AS DEPT, 
           GROUPING(department_id) AS DEPT_GRP, SUM(salary) AS SALARY 
         FROM emp_details_view 
         GROUP BY ROLLUP(department_id) 
         ORDER BY department_id; 
DEPT, DEPT_GRP, SALARY
< 10, 0, 4400 >
< 20, 0, 19000 >
< 30, 0, 24900 >
< 40, 0, 6500 >
< 50, 0, 156400 >
< 60, 0, 28800 >
< 70, 0, 10000 >
< 80, 0, 304500 >
< 90, 0, 58000 >
< 100, 0, 51600 >
< 110, 0, 20300 >
< <NULL>, 1, 684400 >
12 rows found.

The following example shows that you can use the GROUPING function for each column to determine which null values are for the totals.

Command> SELECT department_id AS DEPT, job_id AS JOB, 
           GROUPING(department_id) AS DEPT_GRP, GROUPING(job_id) AS JOB_GRP, 
           GROUPING_ID(department_id, job_id) AS GRP_ID, SUM(salary) AS SALARY 
         FROM emp_details_view 
         GROUP BY CUBE(department_id, job_id) 
         ORDER BY department_id, job_id, grp_id ASC;
 
DEPT, JOB, DEPT_GRP, JOB_GRP, GRP_ID, SALARY
< 10, AD_ASST, 0, 0, 0, 4400 >
< 10, <NULL>, 0, 1, 1, 4400 >
< 20, MK_MAN, 0, 0, 0, 13000 >
< 20, MK_REP, 0, 0, 0, 6000 >
< 20, <NULL>, 0, 1, 1, 19000 >
< 30, PU_CLERK, 0, 0, 0, 13900 >
< 30, PU_MAN, 0, 0, 0, 11000 >
< 30, <NULL>, 0, 1, 1, 24900 >
...
< 110, AC_ACCOUNT, 0, 0, 0, 8300 >
< 110, AC_MGR, 0, 0, 0, 12000 >
< 110, <NULL>, 0, 1, 1, 20300 >
< <NULL>, AC_ACCOUNT, 1, 0, 2, 8300 >
< <NULL>, AC_MGR, 1, 0, 2, 12000 >
< <NULL>, AD_ASST, 1, 0, 2, 4400 >
< <NULL>, AD_PRES, 1, 0, 2, 24000 >
< <NULL>, AD_VP, 1, 0, 2, 34000 >
< <NULL>, FI_ACCOUNT, 1, 0, 2, 39600 >
< <NULL>, FI_MGR, 1, 0, 2, 12000 >
< <NULL>, HR_REP, 1, 0, 2, 6500 >
< <NULL>, IT_PROG, 1, 0, 2, 28800 >
< <NULL>, MK_MAN, 1, 0, 2, 13000 >
< <NULL>, MK_REP, 1, 0, 2, 6000 >
< <NULL>, PR_REP, 1, 0, 2, 10000 >
< <NULL>, PU_CLERK, 1, 0, 2, 13900 >
< <NULL>, PU_MAN, 1, 0, 2, 11000 >
< <NULL>, SA_MAN, 1, 0, 2, 61000 >
< <NULL>, SA_REP, 1, 0, 2, 243500 >
< <NULL>, SH_CLERK, 1, 0, 2, 64300 >
< <NULL>, ST_CLERK, 1, 0, 2, 55700 >
< <NULL>, ST_MAN, 1, 0, 2, 36400 >
< <NULL>, <NULL>, 1, 1, 3, 684400 >
50 rows found.