Oracle SQL - GROUP BY CUBE, ROLLUP: Null Values and GROUPING Function

  1. HOME
  2. Oracle SQL
  3. Select
  4. Group Functions

Introduction

The CUBE and ROLLUP keywords generate many null values in query results.

You can distinguish these system-generated null values from other null values.

For example, you can replace them with some explanatory text by using GROUPING and GROUPING_ID functions. 

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

The GROUPING function can return only two results: 0 or 1. That's why the last two lines both show '**total**'.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w ww . ja  va  2  s . com

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(job)
  3                when 0 then job
  4                when 1 then '**total**'
  5           end job
  6  ,        count(empno) headcount
  7  from     emp
  8  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     | **total** |  00005.00
    2     | MANAGER   |  00001.00
    2     | TRAINER   |  00004.00
    2     | **total** |  00005.00
    30    | ADMIN     |  00001.00
    30    | SALESREP  |  00003.00
    30    | **total** |  00004.00

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

12 rows selected.

SQL>

Related Topic