Cube and Rollup Operator in SQL Server 2008

CUBE operator is used with Group by clause. It use to get the subtotal and grand total of all permutation of columns provided by the CUBE operator.

ROLLUP operator is used with Group by clause. It use to get the subtotal and grand total of a set of columns provided by the ROLLUP operator.

For example,


Declare @tmpTable table(Product varchar(10), Shop varchar(10), Quantity int)
insert into @tmpTable (Product, Shop, Quantity) values ('Tea', 'Tea-Post',100)
insert into @tmpTable (Product, Shop, Quantity) values ('Tea', 'GH2',90)
insert into @tmpTable (Product, Shop, Quantity) values ('Puva', 'Tea-Post',10)
insert into @tmpTable (Product, Shop, Quantity) values ('Puva', 'GH2',25)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Anand',2)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Nadiad',20)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Khambhat',50)
select * from @tmpTable

SELECT Product,Shop,sum(Quantity) FROM @tmpTable GROUP BY ROLLUP (Product,Shop)

SELECT Product,Shop,sum(Quantity) FROM @tmpTable GROUP BY CUBE (Product,Shop)

Result would be

Cube_Rollup_SQL

Share