locked
What is CUBE and ROLLUP??? RRS feed

  • 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
    go

     

    With rollup adds summarizations from left to right:


    select group1, group2, sum(value)
    from   testCubeRollup
    group by group1, group2 with rollup

     

    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

     

    Where 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 cube

    group1      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           30

     

    See, 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
    go

     

    With rollup adds summarizations from left to right:


    select group1, group2, sum(value)
    from   testCubeRollup
    group by group1, group2 with rollup

     

    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

     

    Where 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 cube

    group1      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           30

     

    See, 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