Oracle Using the SQL GROUPING function

Oracle:

Using the GROUPING function

To handle these NULL issues, Oracle 8i introduces a new function called GROUPING. Using a single column as its argument, Grouping returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation.

That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, will return a 0.

GROUPING = 0 is a stored NULL not created by ROLLUP or CUBE

GROUPING = 1 is a NULL value created by ROLLUP or CUBE

Here is a simple example at aggregates employee data.

SELECT

deptno,

job,

count(*),

sum(sal),
GROUPING(Job)
FROM

emp
GROUP BY

ROLLUP(deptno,job);

   DEPTNO JOB         COUNT(*) SUM(SAL) GROUPING(JOB)
--------- --------- --------- --------- -------------
       10 CLERK              1       1300              0
       10 MANAGER            1       2450              0
       10 PRESIDENT          1       5000              0
       10                    1       1000              0
       10                    4       9750              1 <== Aggregate
       20 ANALYST            2       6000              0
       20 CLERK              2       1900              0
       20 MANAGER            1       2975              0
       20                    5      10875              1
       30 CLERK              1        950              0
       30 MANAGER            1       2850              0
       30 SALESMAN           4       5600              0
       30                    6       9400              1 <== Aggregate
                            15      30025              1 <== Aggregate

Here we see that the database has computed aggregate values for each department and also for the total result set.