A simple example of using the Oracle ROLLUP extension

Recently I needed to find out how many rows in a table had a certain status and display a grand total of the number of objects.

In this post, rather than create a demo table with the various status I am going to base my example on the user_objects data dictionary view.

Using the following query I could see the types of objects as well as how many of each object I had.

SELECT object_type,
  FROM user_objects
 GROUP BY object_type

Capture (2)

All that was missing was the total (which should be 37). After various attempts to roll my own query to produce a total which soon started to get unwieldy or over complicated, I knew there must be a better way. A quick search and I found this excellent answer on Stack Overflow from Rob van Wijk which used the Oracle Group By Extension, ROLLUP

Here is the query amended to use ROLLUP, along with the output. Note the total.

SELECT object_type,
  FROM user_objects
 GROUP BY ROLLUP(object_type)


Amending my query to use ROLLUP was trival, (I only had to change line 4) and I now have the total I required produced by a straightforward SQL query.


Rob van Wijk


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.