I learn about CUBE and ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP and CUBE. Lets say you are developing the E-Commerce application and administrator wants the report which shows products purchased by all user group by product and buyer. You will say that’s really easy and can write the query as shown below,
Fig – (1) Group By clause.
Which will returns the result as shown below,
Fig – (2) Result of GROUP BY clause
However what if you want result as display below,
Fig – (3) Desire result
Here ROLLUP and CUBE comes into the picture and help us. The above result is generated using ROLLUP. ROLLUP adds new row for each column used in GROUP BY clause. First have a look at the query and then we will discuss more,
Fig – (4) Query for output shown in fig – 3
As you can see in query we have used ROLLUP after GROUP BY clause. Here ROLLUP has added new row at line 3,5 and 6 in fig 3. If you have used only one columns in GROUP BY clause then only row will have been added. The new clause in query is GROUPING. GROUPING clause add new column in result set. The value in new column can either be 0 or 1. If the new row is added by the ROLLUP or CUBE then the value of GROUPING column is 1 else 0.
In fig – 3 we have total price by user name, wow lets assume you want the total price by item name also. Here you have to use CUBE as shown below,
Fig – (5) CUBE clause
Fig – (6) Result of CUBE
Happy Programming !!!!