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 .
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 .