GROUPING

Syntax

Purpose

This function is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE. These operations produce superaggregate rows that contain nulls representing the set of all values. You can use the GROUPING function to distinguish a null that represents the set of all values in a superaggregate row from an actual null.

The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the GROUPING function is Oracle NUMBER. See the group_by_clause of the SELECT statement for a discussion of these terms.

Example

In the following example, if the GROUPING function returns 1 (indicating a superaggregate row rather than a data row from the table), the string "All Jobs" appears instead of the null that would otherwise appear:

SELECT DECODE(GROUPING(dname), 1, 'All Departments',
         dname) AS dname, 
   DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, 
   COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
   FROM emp, dept 
   WHERE dept.deptno = emp.deptno 
   GROUP BY ROLLUP (dname, job); 

DNAME           JOB       Total Empl Average Sa 
--------------- --------- ---------- ---------- 
ACCOUNTING      CLERK              1      15600 
ACCOUNTING      MANAGER            1      29400 
ACCOUNTING      PRESIDENT          1      60000 
ACCOUNTING      All Jobs           3      35000 
RESEARCH        ANALYST            2      36000 
RESEARCH        CLERK              2      11400 
RESEARCH        MANAGER            1      35700 
RESEARCH        All Jobs           5      26100 
SALES           CLERK              1      11400 
SALES           MANAGER            1      34200 
SALES           SALESMAN           4      16800 
SALES           All Jobs           6      18800 
All Departments All Jobs          14 24878.5714