GROUP BY, CUBE, ROLLUP and SQL SERVER 2005

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,

   1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem)

   2: FROM Orders GROUP BY CustomerName,CustomerName

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,

   1: SELECT

   2:     CASE

   3:     WHEN GROUPING(customername) = 1 THEN 'All Customer'

   4:     ELSE customername END CustomerName,

   5:     CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'

   6:     ELSE itemname END ItemName,

   7: SUM(Quantity*PricePerCase)

   8: FROM orders GROUP BY customername,itemname

   9: WITH ROLLUP

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,

   1: SELECT

   2:     CASE

   3:     WHEN GROUPING(customername) = 1 THEN 'All Customer'

   4:     ELSE customername END CustomerName,

   5:     CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'

   6:     ELSE itemname END ItemName,

   7: SUM(Quantity*PricePerCase)

   8: FROM orders GROUP BY customername,itemname

   9: WITH CUBE

Fig - (5) CUBE clause

Fig - (6) Result of CUBE