Some time we need to summarize data very quickly without writing very complex query. CUBE And ROLL UP are great commands that give us very good summary options.
We can summarize the data without writing dirty and complex queries with the help of CUBE and ROLL Up Operators.
CUBE generates a result set that represents aggregates for all combinations of values in the selected columns.
ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.
Example to see the difference
DECLARE @t TABLE(
ItemName varchar(100),
Color varchar(100),
Combinations int
)
insert into @t values ('Chair','Black',10)
insert into @t values ('Chair','Blue',10)
insert into @t values ('Table','Black',10)
insert into @t values ('Table','Blue',10)
GO
select ItemName,Color,SUM(Combinations) as TotalCnt from @t
Group by ItemName,Color with Rollup
GO
select ItemName,Color,SUM(Combinations) as TotalCnt from @t
Group by ItemName,Color with cube
GO
Rollup added below rows in addition to general group by rows.
Chair NULL 20
Table NULL 20
NULL NULL 40
Cube added below rows in addition to general group by rows.
Chair NULL 20
Table NULL 20
NULL NULL 40
NULL Black 20
NULL Blue 20
Difference between Cube and rollup is, cube calculated the sum of combinations w.r.t both Item and Color, where as rollup calculated the sum of combinations only w.r.t Item. In other words, CUBE evaluates aggregate expression with all possible combinations of columns specified in group by clause, where as the Rollup evaluates aggregate expressions in only the order of columns specified in group by clause.To easily remember this remind the shape of cube and rollup.
Simple and right to the point
ReplyDeleteGood Simple and easy to understand
ReplyDelete