Answered by:
What is CUBE and ROLLUP???

Question
-
Hi,
Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use???
Are they usefull in any point of view from ADO.NET???
Sunday, September 16, 2007 6:41 PM
Answers
-
They are not statements, rather they are modifiers for the GROUP BY clause. They both give you additional summarizations returned from your group by aggregates. Check the following example:
create table testCubeRollup
(
group1 int,
group2 int,
value int
)
insert into testCubeRollup
select 1, 1, 10
union all
select 1, 2, 20
union all
select 1, 3, 30
union all
select 2, 1, 40
union all
select 2, 2, 20
goWith rollup adds summarizations from left to right:
select group1, group2, sum(value)
from testCubeRollup
group by group1, group2 with rollupgroup1 group2
----------- ----------- -----------
1 1 10
1 2 20
1 3 30
1 NULL 60
2 1 40
2 2 20
2 NULL 60
NULL NULL 120Where the nulls are, its summarizing all of thevalues for that group. So 1 NULL 60 is the summarization of the 3 rows with 1. (Check out the GROUPING() function for a way to not just return NULL..)
CUBE goes both directions, rather than just left to right.
select group1, group2, sum(value)
from testCubeRollup
group by group1, group2 with cubegroup1 group2
group1 group2
----------- ----------- -----------
1 1 10
1 2 20
1 3 30
1 NULL 60
2 1 40
2 2 20
2 NULL 60
NULL NULL 120
NULL 1 50
NULL 2 40
NULL 3 30See, it added 3 more rows that were centered on group 2.
Hope this helps
Sunday, September 16, 2007 7:46 PM
All replies
-
CUBE and ROLLUP are sometimes called the super aggregates but these are not functions but operators used for grouping data, CUBE includes duplicate while ROLLUP does not to get NULL values you need to use the GROUPING clause. There are changes coming in 2008 you cannot use the WITH CUBE and WITH ROLLUP versions of these operators because per the 2008 BOL those are not ANSI SQL compliant. The first link is the 2008 BOL and the last is SQL Server SELECT code samples which includes samples of both operators.
http://msdn2.microsoft.com/en-us/library/ms177673(SQL.100).aspx
http://msdn2.microsoft.com/en-us/library/ms187731.aspx
Sunday, September 16, 2007 7:33 PM -
They are not statements, rather they are modifiers for the GROUP BY clause. They both give you additional summarizations returned from your group by aggregates. Check the following example:
create table testCubeRollup
(
group1 int,
group2 int,
value int
)
insert into testCubeRollup
select 1, 1, 10
union all
select 1, 2, 20
union all
select 1, 3, 30
union all
select 2, 1, 40
union all
select 2, 2, 20
goWith rollup adds summarizations from left to right:
select group1, group2, sum(value)
from testCubeRollup
group by group1, group2 with rollupgroup1 group2
----------- ----------- -----------
1 1 10
1 2 20
1 3 30
1 NULL 60
2 1 40
2 2 20
2 NULL 60
NULL NULL 120Where the nulls are, its summarizing all of thevalues for that group. So 1 NULL 60 is the summarization of the 3 rows with 1. (Check out the GROUPING() function for a way to not just return NULL..)
CUBE goes both directions, rather than just left to right.
select group1, group2, sum(value)
from testCubeRollup
group by group1, group2 with cubegroup1 group2
group1 group2
----------- ----------- -----------
1 1 10
1 2 20
1 3 30
1 NULL 60
2 1 40
2 2 20
2 NULL 60
NULL NULL 120
NULL 1 50
NULL 2 40
NULL 3 30See, it added 3 more rows that were centered on group 2.
Hope this helps
Sunday, September 16, 2007 7:46 PM -
Hi Louis,
Ur example realy helps me to understand both these operator.
So, that means if we are using CUBE it will consider all the column that are used to group your data and summarize all columns that are used to group the resultset.
AND
In case of Rollup it will consider only the first column and summarize data of that first column which is present(used) to group your data.
-----------------------------
BUT Tell me one thing that can we use both of these operators only with Grouping statements or querry's or we can use thse in a simple select querry also.
BUT i think we can';t use these in the simple select querry caz as your said these are only used for summarize your data and when i am trying to use these with a simple qyuerry it gives me error.
Wht your opinion???Monday, September 17, 2007 5:20 AM -
You are right. They have to be a part of a group by clause. They cube and rollup the aggregate results from the groupings.
Friday, September 21, 2007 7:19 AM