Sql Aggregations

SQL Aggregations for DWH - group by rollup/cube

Friday, September 23, 2016

Today I learned something new on my current project. I remember I once got a question on an exam in college to build a report including all subtotals, purely in SQL. This was a very complex assignment including a huge list of union statements. In the end it worked, but I couldn't say I was very proud of the solution.

Today I discovered the group by extensions: Cube and Rollup. These extensions basically calculate subtotals of your group by columns. I'll use a small data example to illustrate.

If we now want to see what the total of damages is, with the subtotals per team, we can create 2 group by statements and combine them using a union, or we can use the following;

will give:

One simple statement in stead of a complex structure of statements! The global total is included as an ‘empty’ team, but a simple nvl can change this team name. The group by rollup is basically a simple extension to the group by clause.

The goup by cube goes a step further and creates subtotals for all the possible combinations in the group by cube statement.

yields

You can clearly see that there are subtotals for all the possible combinations of team and real_name. If you work a lot with cross-tabulation, this can come in handy! There are a lot more possibilities built into Oracle. You can find more information here:

https://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#i1007428

 

Good luck!