Oracle SQL - GROUPING_ID Function

Introduction

The GROUPING_ID function is more flexible than the GROUPING function, because it can return several different results.

select   deptno
,        case GROUPING_ID(deptno, job)
              when 0 then job
              when 1 then '**dept **'
              when 3 then '**total**'
         end  job
,        count(empno) headcount
from     emp
group by rollup(deptno, job);

GROUP BY ROLLUP can produce only 1 (binary 01) and 3 (binary 11), but GROUP BY CUBE can also generate 2 (binary 10).

GROUPING_ID produces a 0 (zero) for all "regular" rows in the result.

GROUPING_ID Function Example with CUBE

select   deptno, job
,        GROUPING_ID(deptno, job) gid
from     emp
group by cube(deptno, job);

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w w  w.  j  a v  a2s . c om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL> select   deptno
  2  ,        case GROUPING_ID(deptno, job)
  3                when 0 then job
  4                when 1 then '**dept **'
  5                when 3 then '**total**'
  6           end  job
  7  ,        count(empno) headcount
  8  from     emp
  9  group by rollup(deptno, job);

   DEPTNO | JOB       | HEADCOUNT
--------- | --------- | ---------
    1     | ADMIN     |  00001.00
    1     | MANAGER   |  00002.00
    1     | DIRECTOR  |  00001.00
    1     | SALESREP  |  00001.00
    1     | **dept ** |  00005.00
    2     | MANAGER   |  00001.00
    2     | TRAINER   |  00004.00
    2     | **dept ** |  00005.00
    30    | ADMIN     |  00001.00
    30    | SALESREP  |  00003.00
    30    | **dept ** |  00004.00

   DEPTNO | JOB       | HEADCOUNT
--------- | --------- | ---------
 [N/A]    | **total** |  00014.00

12 rows selected.

SQL>
SQL> select   deptno, job
  2  ,        GROUPING_ID(deptno, job) gid
  3  from     emp
  4  group by cube(deptno, job);

   DEPTNO | JOB      |       GID
--------- | -------- | ---------
 [N/A]    |  [N/A]   |  00003.00
 [N/A]    | ADMIN    |  00002.00
 [N/A]    | MANAGER  |  00002.00
 [N/A]    | TRAINER  |  00002.00
 [N/A]    | DIRECTOR |  00002.00
 [N/A]    | SALESREP |  00002.00
    1     |  [N/A]   |  00001.00
    1     | ADMIN    |  00000.00
    1     | MANAGER  |  00000.00
    1     | DIRECTOR |  00000.00
    1     | SALESREP |  00000.00

   DEPTNO | JOB      |       GID
--------- | -------- | ---------
    2     |  [N/A]   |  00001.00
    2     | MANAGER  |  00000.00
    2     | TRAINER  |  00000.00
    30    |  [N/A]   |  00001.00
    30    | ADMIN    |  00000.00
    30    | SALESREP |  00000.00

17 rows selected.

SQL>

Related Topic