Tip

The power of the ROLLUP and CUBE function

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.