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
/

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)
/

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