ROLLUP(),CUBE()

Oracle 8i introduced rollup, cube clauses these are optional clauses used along with group by clause these clauses are used to calculate subtotal, grand total automatically.

Syntax:

ROLLUP

SELECT col1,col2....
FROM tablename 
GROUP BY ROLLUP(col1,col2...);

CUBE

SELECT col1,col2.... 
FROM tablename 
GROUP BY CUBE(col1,col2...);

Note 1:

When ever group by clause having single column then rollup, cube clauses return same results in this case these clauses returns grand total automatically.

Example 1:

SQL> Select deptno,sum(sal) from emp GROUP BY ROLLUP(deptno);

Output:

    DEPTNO   SUM(SAL)
---------- ----------
        10       8750
        20      10875
        30       9400
                29025

Example 2:

SQL> Select deptno,sum(sal) from emp GROUP BY CUBE(deptno);

Output:

    DEPTNO   SUM(SAL)
---------- ----------
                29025
        10       8750
        20      10875
        30       9400

Note 2:

Generally rollup is used to calculate subtotal, grand total based on a single column at a time where as if you want to calculate subtotal, grand total based on all  GROUP BY clause columns then we are using CUBE.

SQL>  select deptno,job,sum(sal) from emp GROUP BY deptno,job;

Output:

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        20 CLERK           1900
        30 SALESMAN        5600
        20 MANAGER         2975
        30 CLERK            950
        10 PRESIDENT       5000
        30 MANAGER         2850
        10 CLERK           1300
        10 MANAGER         2450
        20 ANALYST         6000

Example 1:

SQL> select deptno,job,sum(sal) from emp GROUP BY ROLLUP(deptno,job);

Output:

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 CLERK           1900
        20 ANALYST         6000
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025

Example 2:

SQL> select deptno,job,sum(sal) from emp GROUP BY ROLLUP(job,deptno);

Output:

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        20 CLERK           1900
        30 CLERK            950
           CLERK           4150
        20 ANALYST         6000
           ANALYST         6000
        10 MANAGER         2450
        20 MANAGER         2975
        30 MANAGER         2850
           MANAGER         8275
        30 SALESMAN        5600
           SALESMAN        5600
        10 PRESIDENT       5000
           PRESIDENT       5000
                          29025

Example 3:

SQL> SELECT deptno,job,sum(sal),count(*) from emp 
GROUP BY CUBE(deptno,job) 
ORDER BY 1,2;

Output:

    DEPTNO JOB         SUM(SAL)   COUNT(*)
---------- --------- ---------- ----------
        10 CLERK           1300          1
        10 MANAGER         2450          1
        10 PRESIDENT       5000          1
        10                 8750          3
        20 ANALYST         6000          2
        20 CLERK           1900          2
        20 MANAGER         2975          1
        20                10875          5
        30 CLERK            950          1
        30 MANAGER         2850          1
        30 SALESMAN        5600          4
        30                 9400          6
           ANALYST         6000          2
           CLERK           4150          4
           MANAGER         8275          3
           PRESIDENT       5000          1
           SALESMAN        5600          4
                          29025         14