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);
SQL> SQL> drop table emp; Table dropped.-- w ww. j a v a 2s . c o m 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>