The Difference Between Rollup and Cube

Question

The Difference Between Rollup and Cube

Ben Richardson

SSC Enthusiast

Points: 100
More actions
November 29, 2017 at 10:49 pm

#402715

Comments posted to this topic are about the item The Difference Between Rollup and Cube
November 30, 2017 at 7:20 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

gfabbri SSC Enthusiast Points: 187 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 76996 More actions · Answer 4

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: 1004406 More actions · Answer 5

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.

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

csnetsurfer SSC Enthusiast Points: 101 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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: 90860 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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

gowtham Newbie Points: 2 More actions · Answer 12

Really helpful in understanding the topic.The content is good and understandable.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

gowtham wrote:

Really helpful in understanding the topic.The content is good and understandable.

Awesome!. What did you actually like about "the content" and how was it "really helpful"?

--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.

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