The Difference Between Rollup and Cube

  • Ben Richardson

    SSC Journeyman

    Points: 90

    Comments posted to this topic are about the item The Difference Between Rollup and Cube

  • gfabbri

    SSC Enthusiast

    Points: 157

    very interesting. The point is the order of fields in the GROUP BY ROLLUP, that establishes the hierarchy.
    In order to get the same result you get with CUBE using ROLLUP, you need to merge two select in a UNION:

    SELECT
    coalesce (department, 'All Departments') AS Department,
    coalesce (gender,'All Genders') AS Gender,
    sum(salary) as Salary_Sum
    FROM employee
    GROUP BY ROLLUP (gender,department)
    UNION
     SELECT
    coalesce (department, 'All Departments') AS Department,
    coalesce (gender,'All Genders') AS Gender,
    sum(salary) as Salary_Sum
    FROM employee
    GROUP BY ROLLUP (department,gender)

    The two SELECTs are in all equal to each other, except for the order of fields in GROUP BY ROLLUP.

    You can say that conceptually CUBE is more similar to GROUP BY, in the sense that the order of fields 
    is unrelevant. Using ROLLUP you must put some care in the effects of different permutations.

  • Jacque.Murrell

    SSC Enthusiast

    Points: 116

    I have never used either of these functions but in my current position I can see lots of places where I can utilize them.
    Thanks, great stuff.

  • bpwilso

    SSC-Addicted

    Points: 448

    Nice right up.  I've used these two functions to optimize a few procedures we have floating around.  It resulted in a lesson to the rest of the staff on newer sql functions available and prompted us to update.

  • drew.allen

    SSC Guru

    Points: 76739

    bpwilso - Thursday, November 30, 2017 12:38 PM

    Nice right up.  I've used these two functions to optimize a few procedures we have floating around.  It resulted in a lesson to the rest of the staff on newer sql functions available and prompted us to update.

    NEW?!?  Some version of CUBE/ROLLUP has been around since I started using SQL (6.5 or 7) back in 1999.  The syntax changed when they introduced GROUPING SETS in SQL 2008(?), but the functionality hasn't really changed.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeff Moden

    SSC Guru

    Points: 997178

    gfabbri - Thursday, November 30, 2017 12:21 AM

    very interesting. The point is the order of fields in the GROUP BY ROLLUP, that establishes the hierarchy.
    In order to get the same result you get with CUBE using ROLLUP, you need to merge two select in a UNION:

    SELECT
    coalesce (department, 'All Departments') AS Department,
    coalesce (gender,'All Genders') AS Gender,
    sum(salary) as Salary_Sum
    FROM employee
    GROUP BY ROLLUP (gender,department)
    UNION
     SELECT
    coalesce (department, 'All Departments') AS Department,
    coalesce (gender,'All Genders') AS Gender,
    sum(salary) as Salary_Sum
    FROM employee
    GROUP BY ROLLUP (department,gender)

    The two SELECTs are in all equal to each other, except for the order of fields in GROUP BY ROLLUP.

    You can say that conceptually CUBE is more similar to GROUP BY, in the sense that the order of fields 
    is unrelevant. Using ROLLUP you must put some care in the effects of different permutations.

    If you depend on the implicit return order of either, you could end up in deep Kimchi.  Take a look at the GROUPING() function to see what can be done there.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • csnetsurfer

    SSC Enthusiast

    Points: 101

    I think it is very interesting that you can group multiple hierarchies and can see where this can be very useful! Thank you so much for sharing, your awesome! Moreover, I love that you explain the differences!

  • Dennis Wagner-347763

    SSC Eights!

    Points: 945

    Nice article.

    The only thing I don't like is your use of COALESCE.  If there happens to be a NULL department (as the result of a left join), then it will show up as "All Departments."  There is a function named GROUPING that will let you test the group level.  So instead of this:

    coalesce (department, 'All Departments') AS Department,

    You could use this:
    CASE GROUPING(department)  WHEN 1 THEN 'All Departments' ELSE department END AS Department

  • MRitch

    Ten Centuries

    Points: 1181

    So I have tried this query and I repeatedly get the error "Invalid column name". Everything I have read indicates that you can't use alias in the group by since the select is processed later in the order of the query. So what am I missing here that will make this work "as is"?

    SELECT

    coalesce (department, 'All Departments') AS Department,

    coalesce (gender,'All Genders') AS Gender,

    sum(salary) as Salary_Sum

    FROM employee

    GROUP BY ROLLUP (department, gender)

     

     

    For help on getting help, follow the link below:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Sue_H

    SSC Guru

    Points: 90779

    How the grouping and aliases works is correct but in this case you are grouping by columns that do exist. The example is the same as doing it without the aliases:

      SELECT
    coalesce (department, 'All Departments') ,
    coalesce (gender,'All Genders'),
    sum(salary) as Salary_Sum
    FROM employee
    GROUP BY ROLLUP (department, gender)

     

    Are you executing against an employee table that has a department, salary and gender column? Same spelling? Or same case if needed depending on your collation?

    Sue

     

     

  • MRitch

    Ten Centuries

    Points: 1181

    Removing the alias makes perfect sense, oh and it works perfectly! This will make my reporting a lot easier!

    Thank you Sue for the insight and Ben for the article!

    For help on getting help, follow the link below:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • AdeDBA

    Newbie

    Points: 6

    Very useful post, I have never really fully understood cube but this post has helped me understand,, as it is explained clearly with good examples. I definately have some testing to do! Thanks for sharing

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply