Home » SQL SERVER ROLLUP

SQL SERVER ROLLUP

SQL SERVER ROLLUP clause is used to display subtotals and grand totals in the result set. It is always used with GROUP BY CLAUSE.





Group by can not aggregate data on multiple levels of a hierarchy’s like to display sub total (one level of hierarchy) and grand total  (two levels of hierarchy) in the result set , for example we can calculate the total of all purchased for each Item (one level of hierarchy)  but cannot calculate the total purchased of all Item regardless of what item it is (two levels of hierarchy).

In such case , you can use ROLLUP clause as it extend the functionality of  GROUP BY CLAUSE  by calculating subtotals and grand totals for a set of columns . 

SYNTAX

SELECT

column1 ,  column2 ,  ..  ,  aggregate_function (columnname)

FROM tablename 

GROUP BY  ROLLUP (ccolumn1 , Column2 ,..., );

Lets look at an example of ROLL UP in SQL

Select Item , Sum(Item_Count) as Total from itemdetails
Group by Item

 

As you can see , above Group By statement can only returns the total purchased for each item, but can not display the overall total of item purchased.

Following ROLLUP clause extends the functionality of Group By clause and display the grand total also.

Select Item , Sum(Item_Count) as Total

From itemdetails

Group by ROLLUP (Item)

 

 

As you can see , it groups the total purchased for each Item as well as display the grand total of all purchased item , It adds a extra row in result set to display the total and subtotal .

You can also display any custom description by replacing the null value with TOTAL to make a result set more clear and meaningful.

Select Coalesce (Item ,'TOTAL' ) as Item, Sum(Item_Count) as Total

From itemdetails

Group by ROLLUP (Item)

 

 

To display the Total purchased for each item in each year with grand total and over all total purchased of Item

Select coalesce( Item ,'GRAND TOTAL' ) As Item,  

Coalesce( CAST( Year (ItemPurchasedDate) As VARCHAR) ,'TOTAL') as Year 

, Sum(Item_Count) as Total From itemdetails 

Group By RollUp (Item,Year(ItemPurchasedDate) )

 

 

As you can see, it returns the result with total purchased for item in each year with grand total as well as over all total of purchased items .




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.