ROLLUP gives an aggregate value by using the order of columns in a GROUP BY clause. ROLL UP will pass one record at a time
If we have Col1, Col2 and Col3 in Group BY and if we have a hierarchical data and we want to roll up our data in their given order we could do
SELECT Col1, Col2, Col3, Count(*) FROM Table1 GROUP BY rollup( Col1, Col2, Col3 ); |
Or we could specify the above query as
SELECT Col1, Col2, Col3, Count(*) FROM Table1 GROUP BY Col1, Col2, Col3 WITH rollup; |
Then the result of aggregate will be summarized as
Col1, Col2, Col3,Count(*)
Col1, Col2,Count(*)
Col1,Count(*)
Let us take one practical example from AdventureWorks database. Both scripts below give the same result set.
--rollup style1 SELECT t.[group] AS Region, t.countryregioncode AS Country, t.name AS TerritoryName, Sum(sh.totaldue) TotalSales FROM sales.salesorderheader sh JOIN sales.salesterritory t ON sh.territoryid = t.territoryid WHERE t.[group] = 'Europe' GROUP BY rollup( t.[group], t.countryregioncode, t.name ) ; go --rollup style2 SELECT t.[group] AS Region, t.countryregioncode AS Country, t.name AS TerritoryName, Sum(sh.totaldue) TotalSales FROM sales.salesorderheader sh JOIN sales.salesterritory t ON sh.territoryid = t.territoryid WHERE t.[group] = 'Europe' GROUP BY t.[group], t.countryregioncode, t.name WITH rollup; go |
As you can see above rollup passes one row at a time and create a grouping sets based on how the columns appear in the group by clause.
Read More
http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx