Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

ROLLUP feature in Oracle 8i

An example of the ROLLUP feature in Oracle 8i.

If you wish to find, for example, the number of employees by department, you can use the following query (using the standard demo tables in Oracle).

 SELECT deptno, count(*)
   FROM emp
 GROUP BY deptno;
To include the total number of employees, you need to use the UNION clause as follows:
 SELECT deptno, count(*)
  FROM emp
 GROUP BY deptno
 SELECT to_number(null), count(*)
 FROM emp;
In Oracle8i, you can use the ROLLUP option to achieve the same result:
 SELECT deptno, count(*)
  FROM emp
 GROUP BY rollup(deptno);
By simply invoking the ROLLUP facility in the GROUP BY clause, we direct Oracle to summarize the data at levels above the columns specified, all the way to the grand total.

Note that Oracle reports the grand total by leaving the GROUP BY column blank. If the GROUP BY column also contains null values, it might be difficult to distinguish those values from the grand total row. Fortunately, you can use a special function called GROUPING to report the current summarization level. The function returns two values: 0 indicates that the current row is grouped at the specified GROUP BY level, and 1 indicates that the row is grouped at a higher level.

 SELECT deptno, count(*), grouping(deptno)
  FROM emp
 GROUP BY rollup(deptno);
  ----   ----      ----
  10     3         0
  20     5         0
  30     6         0
         14        1

For More Information

  • What do you think about this tip? E-mail the editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.