ROLLUP Clause in SQL Server

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