SQL 101 - Group by ROLLUP

One of the reasons I called renamed this blog grassroots-oracle was to occasionally illustrate some simple examples of functionality not everyone is aware of - or are maybe too scared to give it a go (or research).
Still today I occasionally work with people who aren't familiar with some features I almost take for granted.

Today's little observation was with ROLLUP - a function used in group by queries to provide sub-totals.

I was looking at log frequency by month, and I also needed to consider grand total, so consider:

Rollup folks!

It's one of those features in the data warehousing guide that should possibly be in an area more accessible. While I don't necessarily use it for production code - it's syntax that I often find handy with ad hoc queries while interrogating the database.

select count(*), trunc(view_date,'mm') 
from activity_log 
group by rollup(trunc(view_date,'mm')) 
order by 2 desc nulls last

You'll also want to check out single row functions such as GROUPING.


Go nuts.