# 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 last published in March 2001

## Content

Find more PRO+ content and other member only offers, here.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Amazon ushers in pay-by-query pricing with Athena data engine

Amazon's Athena data engine brings interactive SQL queries to S3. It's based on an open source framework that Teradata and others...

• ### A look at the EnterpriseDB Postgres data management platform

The EDB Postgres platform, which is based on the PostgreSQL open source relational database, is offered as a subscription service...

• ### Louisiana uses GIS software to create map to flood preparedness

The Louisiana Department of Health responded to flooding with the help of GIS software that located trouble spots with at-risk ...

## SearchBusinessAnalytics

• ### Business should work with IT to get self-service BI governance right

Self-service analytics is all about getting IT roadblocks out of the way of the business, but traditional tech teams can still ...

• ### Creative projects leave people guessing about future impact of AI

A push is underway to write creative AI algorithms that can engage in music, film and design projects. So far, they have ...

• ### Data visualization plays an important role in a big data environment

Enterprises are increasingly putting data visualization tools, like Tableau, at the top of their big data platforms to start ...

## SearchSAP

• ### Does SAP ONE Support Launchpad make SAP support any easier to use?

The new Fiori user experience makes it easier to access applications and support services, but product-specific support still ...

• ### At SME Summit, SAP Anywhere growing pains revealed

Attendees at an SAP SME Summit lauded the e-commerce front end, but some said back-end integration and payment-processing ...

• ### Planning a HANA big data strategy with SAP HANA Vora

SAP has worked hard to position HANA as a big data platform. To formulate a viable big data strategy, you need to know the tools,...

## SearchSQLServer

• ### How to get the most out of virtual SQL Server with Microsoft Hyper-V

SQL Server is a CPU-intensive technology, which can make it tricky to run in a virtualized environment. Keep your SQL Server ...

• ### Microsoft previews SQL Server on Linux, opens features across editions

Microsoft looks to broaden the horizons of SQL Server, as it moves some Enterprise features to Standard Edition and issues the ...

• ### Cask framework aims to speed Azure HDInsight data pipeline builds

A link between Cask Data's CDAP application and integration environment and Azure HDInsight, Microsoft's Hadoop cloud service, is...

## TheServerSide

• ### How to turn your DevOps failures into ALM successes

Doing the right thing doesn't always mean you're doing things right. But don't fret, because short-term DevOps failures can mean ...

• ### From chatbots to IBM's Watson: How software deals with conversational language

The next big thing in software development is conquering the conversational language development hurdle. Here's how the big ...

• ### Getting through the three toughest stages of a DevOps transition

Looking to gain increased productivity and software quality by embracing DevOps? Here are the DevOps transitions your company ...

## SearchDataCenter

• ### Compare benefits of a blade server architecture vs. hyper-convergence

To determine if a series of blade servers is the right call for more condensed compute in your data center, evaluate its benefits...

• ### A hyper-converged data center offers SDDC opportunity, IT scalability

Hyper-convergence can increase scalability and pave the way toward software-defined infrastructure. Use these five tips to learn ...

• ### OpenStack enterprise adoption still awaits full embrace

OpenStack in the enterprise is more likely to see continued adoption via vendor distributions and managed services, not the raw ...

## SearchContentManagement

• ### Five steps for designing an effective BI dashboard

The BI dashboard can be a very useful tool for data engagement if the dashboard is designed and used effectively. Here are five ...

• ### Examining the top offerings in the WCM platform marketplace

Once you've decided that a WCM platform is right for your business, it's time to decide which to choose. Expert Geoffrey Bock ...

• ### Why Microsoft Teams could change up the collaboration software market

Microsoft's new collaboration tool, Teams, could give Slack a run for its money. Expert Reda Chouffani offers a look at five ...

## SearchFinancialApplications

• ### No one-size-fits-all strategy for cloud ERP software migration

Experts say a cloud ERP transition plan will vary according to a variety of factors, from company size to an organization's ...

• ### HR video apps spreading beyond training to recruiting, engagement

Users say cost savings, faster turnaround and better corporate branding are some of the benefits from using video at more stages ...

• ### Microsoft picks SAP for core HCM tools and global talent management

Microsoft chooses SAP SuccessFactors for core HR and talent management, and SAP selects Microsoft Azure as a preferred public ...

Close