# SQL Tutorial ROLLUP and CUBE

2020-05-09 Rahul Swami

# 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``````