CUBE generates a result set that represents aggregates for all combinations of values in the selected columns. CUBE automatically generates a summation rollup to the top level of a GROUP BY.
Lets us look one Example.
Code:
Create table #TempTable(Items varchar(30),
Model varchar(10),
Price float,
Quantity int)
Insert into #TempTable values('Furniture','Chair',100,1)
Insert into #TempTable values('Furniture','Chair',200,1)
Insert into #TempTable values('Furniture','Table',300,2)
Insert into #TempTable values('Electronic Item','Pendrive',450,2)
Insert into #TempTable values('Electronic Item','Pendrive',150,1);
Select
Case
When grouping(Items) = 1 then 'All Items'
Else Items End 'Item List',
Case
When grouping(Model) = 1 then 'All Model'
Else Model End Models,
Sum(Price*Quantity) as Total
From #TempTable
Group by Items,
Model
With Cube
Drop table #TempTable