Oracle Analytic Function - Oracle/PLSQL ROLLUP Function

ROLLUP returns a row containing a subtotal for each group of rows and a row containing a grand total for all the groups.

Example

The following code shows how to use ROLLUP with single Column.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
--   www .  ja v  a  2 s. co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT deptno, SUM(sal)
  2  FROM emp
  3  GROUP BY ROLLUP(deptno)
  4  ORDER BY deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        10       5850
        20       8275
        30       8450
                22575

SQL>

Example 2

The following code shows how to use ROLLUP and multiple columns.


SQL> SELECT deptno, job, SUM(sal)
  2  FROM emp--  ww w .ja v  a  2  s.  c  o  m
  3  GROUP BY ROLLUP(deptno, job)
  4  ORDER BY deptno, job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 MANAGER         2850
        10 PRESIDENT       3000
        10                 5850
        20 ANALYST         3000
        20 CLERK           2300
        20 MANAGER         2975
        20                 8275
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 8450
                          22575

11 rows selected.

SQL>

Changing the Position of Columns Passed to ROLLUP.


SQL> SELECT job, deptno, SUM(sal)
  2  FROM emp-- from   w  ww.ja va2 s. c o  m
  3  GROUP BY ROLLUP(job, deptno)
  4  ORDER BY job, deptno;

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
ANALYST           20       3000
ANALYST                    3000
CLERK             20       2300
CLERK                      2300
MANAGER           10       2850
MANAGER           20       2975
MANAGER           30       2850
MANAGER                    8675
PRESIDENT         10       3000
PRESIDENT                  3000
SALESMAN          30       5600
SALESMAN                   5600
                          22575

13 rows selected.

SQL>

Using Aggregate Functions with ROLLUP


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from   w w w .  ja v a 2 s  . co  m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL>
SQL> SELECT deptno, job, AVG(sal)
  2  FROM emp
  3  GROUP BY ROLLUP(deptno, job)
  4  ORDER BY deptno, job;

    DEPTNO JOB         AVG(SAL)
---------- --------- ----------
        10 MANAGER         2850
        10 PRESIDENT       3000
        10                 2925
        20 ANALYST         3000
        20 CLERK           1150
        20 MANAGER         2975
        20              2068.75
        30 MANAGER         2850
        30 SALESMAN        1400
        30                 1690
                     2052.27273

11 rows selected.

SQL>

Using a Column Multiple Times in a ROLLUP Clause

Using a column many times in a ROLLUP clause get report on different groupings of data.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- w  w  w  .  ja va2 s.  com
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL> SELECT deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY deptno, ROLLUP(deptno, job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 MANAGER         2850
        10 PRESIDENT       3000
        20 CLERK           2300
        20 ANALYST         3000
        20 MANAGER         2975
        30 MANAGER         2850
        30 SALESMAN        5600
        10                 5850
        20                 8275
        30                 8450
        10                 5850
        20                 8275
        30                 8450

13 rows selected.

SQL>