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,
       COUNT(*)
  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,
       COUNT(*)
  FROM user_objects
 GROUP BY ROLLUP(object_type)
/

Capture2

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.

Acknowledgements

Rob van Wijk

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

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

%d bloggers like this: