Oracle SQL - GROUP BY ROLLUP

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

Introduction

Consider the following script, regular GROUP BY. 

select   deptno, job
,        count(empno) headcount
from     emp
group by deptno, job;

The script shows the number of emp per department and within each department per job.

GROUP BY ROLLUP

The following script adds ROLLUP to the GROUP BY. 

select   deptno, job
,        count(empno) headcount
from     emp
group by ROLLUP(deptno, job);

The ROLLUP addition results in four additional rows.

Three of these four additional rows show the head count per department over all jobs, and the last row shows the total number of emp.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  w  w.ja v a 2  s.  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, job
  2  ,        count(empno) headcount
  3  from     emp
  4  group by deptno, job;

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

8 rows selected.

SQL>
SQL> select   deptno, job
  2  ,        count(empno) headcount
  3  from     emp
  4  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     |  [N/A]   |  00005.00
    2     | MANAGER  |  00001.00
    2     | TRAINER  |  00004.00
    2     |  [N/A]   |  00005.00
    30    | ADMIN    |  00001.00
    30    | SALESREP |  00003.00
    30    |  [N/A]   |  00004.00

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

12 rows selected.

SQL>

Related Topic