The power of the ROLLUP and CUBE function

Understand the power of the ROLLUP and CUBE functions with this discussion and examples.

To appreciate the power of the ROLLUP and CUBE functions, consider the following SQL statement:

ora816 SamSQL :> COMPUTE sum of totsal on deptno
ora816 SamSQL :> BREAK on deptno
ora816 SamSQL :> SELECT deptno,job,sum(sal) 
                 totsal from emp group by deptno,job;

    DEPTNO JOB           TOTSAL
---------- --------- ----------
        10 CLERK           1300
           MANAGER         2450
           PRESIDENT       5000
**********           ----------
sum                        8750
        20 ANALYST         6000
           CLERK           1900
           MANAGER         2975
**********           ----------
sum                       10875
        30 CLERK            950
           MANAGER         2850
           SALESMAN        5600
**********           ----------
sum                        9400

Now compare the use of the ROLLUP function:

ora816 SamSQL :> select deptno,job,sum(sal) 
                 totsal from emp group by ROLLUP(deptno,job);

    DEPTNO JOB           TOTSAL
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750   Total of Deptno 10
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025   Grand Total

If you compare the two outputs, you will notice that you are getting the same results. By using ROLLUP you can avoid using the COMPUTE and GROUP BY functions from SQL. This will mostly helpful in PL/SQL.

Now let's look at the use of the CUBE function:

ora816 SamSQL :> select deptno,job,sum(sal) 
                 totsal from emp group by CUBE(deptno,job);

    DEPTNO JOB           TOTSAL
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750   Total of Deptno 10
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
           ANALYST         6000
           CLERK           4150
           MANAGER         8275    Total w.r.t JOB
           PRESIDENT       5000
           SALESMAN        5600
            Grand Total   29025

CUBE also does a total with respect to the second group--JOB in our case. Finally, you will see the grand total at the end.

In conclusion, ROLLUP and CUBE are aggregate functions that allows developers and DBA's avoid COMPUTE and GROUP BY functions and thus simplify programming logic.

 

For More Information

  • What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
  • The best Oracle Web links: tips, tutorials, and much more.
  • Have another Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Do you have any technical questions about Oracle administration or development? Post them--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature! Our Oracle gurus are waiting to answer your toughest Oracle questions.


 

This was first published in March 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close