I learn about CUBE and ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP and CUBE. Lets say you are developing the E-Commerce application and administrator wants the report which shows products purchased by all user group by product and buyer. You will say that’s really easy and can write the query as shown below,
1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem)
2: FROM Orders GROUP BY CustomerName,CustomerName
Fig – (1) Group By clause.
Which will returns the result as shown below,
Fig – (2) Result of GROUP BY clause
However what if you want result as display below,
Fig – (3) Desire result
Here ROLLUP and CUBE comes into the picture and help us. The above result is generated using ROLLUP. ROLLUP adds new row for each column used in GROUP BY clause. First have a look at the query and then we will discuss more,
1: SELECT
2: CASE
3: WHEN GROUPING(customername) = 1 THEN 'All Customer'
4: ELSE customername END CustomerName,
5: CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
6: ELSE itemname END ItemName,
7: SUM(Quantity*PricePerCase)
8: FROM orders GROUP BY customername,itemname
9: WITH ROLLUP
Fig – (4) Query for output shown in fig – 3
As you can see in query we have used ROLLUP after GROUP BY clause. Here ROLLUP has added new row at line 3,5 and 6 in fig 3. If you have used only one columns in GROUP BY clause then only row will have been added. The new clause in query is GROUPING. GROUPING clause add new column in result set. The value in new column can either be 0 or 1. If the new row is added by the ROLLUP or CUBE then the value of GROUPING column is 1 else 0.
In fig – 3 we have total price by user name, wow lets assume you want the total price by item name also. Here you have to use CUBE as shown below,
1: SELECT
2: CASE
3: WHEN GROUPING(customername) = 1 THEN 'All Customer'
4: ELSE customername END CustomerName,
5: CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
6: ELSE itemname END ItemName,
7: SUM(Quantity*PricePerCase)
8: FROM orders GROUP BY customername,itemname
9: WITH CUBE
Fig – (5) CUBE clause
Fig – (6) Result of CUBE
Happy Programming !!!!
very good explanation
There is error on the first query. then all the information is good
Thanx a lot…it’s really a nice example…
Hey, I came to know about a entirely a new concept in sql server and also the explantion is good.
gr8
good explanation – From Malaysia 🙂
Very understandable explanation, really very useful…
Thanks for sharing, nice job!
One question for you, if I want to add a column, which is the percentage based on each customername, what should I do? I mean like,
Jacob, item1 is $312, how can I get the percentage of the total $792.5.
Thanks a lot!!
V good………
nice job guys…..
It helps a lots of people……… 🙂
very helpful illustration!!
Short & understandable concept…thnks to whole team.
Really nice example for studying.
hey,
very good explanation..explained in a very simple way.it helps me a lot
thx for the whole team
here there is some problem..
change Itemname insted of CustomerName..
all other is good
thanks
1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem)
2: FROM Orders GROUP BY CustomerName,CustomerName
Hi,
I tried to use rollup function but got this error on sql 2005
Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates.
pch 😦
It was really a good example . I was more interested in CUBE.
it was gr8.
G8 and Five Star Answer 🙂
hi,
you r really doing a gr8 job. this example helps me a lot.
thanks
thanks for sharing this article………..really a good example…..
given a star schema with four dimensions,how many select-sum-group by statements will be replaced by a select-sum-group by cube statement?
Reply to my email ASAP
Very Nice Information
Really I am impressed with this .
Hi Guys
ur team has done a great job….
Am reall impressed with ur xplanation,it helped me a lot.
Thanq
hi anusha
fb : umehta71@gmail.com
With this i resolve my problem
it really working fine and reduced lot of coding
Thanks
Nice explination.
Thanks for such a nice post…..
simple example and very useful. thank u..!
I have a question,
how do you build a table (in the fastest method) when your columns are “customername”, rows are “itemname” and the values are the prices you have just calculated?
Dude, just create the column names in a spreadsheet and add the data as rows underneath. Then use the Data Import/Export Wizard to get it in. It will create the table and the data rows. Alternatively, learn DDL and write a create statement to to it.
Totally not normalised but useful to get the data in as described above.
Oh, also, don’t forget to rename the worksheet to Orders or the table name will be Sheet$1
nice work on the renaming of the cube, first time i see that
Nice explination.Very Useful ……..Very Helpful to Others
Good Explanation… Easy to learn.
Nice Article ………..Very-2 thanks
simple & clear explanation
pllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllz tell me how i can cube using SQl 2005 and .net 2008
plllllllllllllllllllllllllllllz
sorry how can i create cube
Good article and easy to understand and please post more articles
thanks for uploading these type artical
Very nice artical
Good Job.
The article is clearly explained. Thanks
i need to understand the signifiance of NULL while using CUBE please help
Nice example buddy…
btw… which one of is you amongst 5 guys on the top?
and wts d reason… everyone has bandage in their fingers?
Second from Right, and he is having band because it was Kite festival day in Gujarata , Ahmedabad.
Sorry He is second from left having scarf in his neck.
is there any Performance issue by using GROUPING in select statement
Thanks Ncie article
Nice Explanation …
Really good for beginners . . . . . . . .Thanks a lot
Excellent,Every one can understand good explanation . . . . .
Indeed a great example.
However, this fails if the first two columns are not of varchar type.
Suppose ItemNo is of int data type and we write
CASE WHEN GROUPING(ItemNo)=1 then ‘Item Number’ ELSE ItemNo END AS ITEM_NUMBER
Then, error will be generated at “Cnversion fails in converting ‘ItemNo’ in int datatype.
Do you have solution of this ??
You can do this
CASE WHEN GROUPING(ItemNo)=1 then ‘Item Number’ ELSE Cast(ItemNo as Varchar(10)) END AS ITEM_NUMBER
you can use
case when grouping(itempno)=1 then to_char(itemno) end as item_number
hi sir you have done good programming for the ROLLUP and CUBE clause thanks
alert(‘boo!…’)
Thank you so much for this explanation. I had to learn those for my college exam. The teacher’s explanation was really bad!
very nice and usfull posts.
thanks for all of posters
Nice Example
how to user Order By , i want PRICE in descending order
It really helped me. Thanks!
Really Very good explanation. Wishes and good job Man…
Thanks lot !!!
Very good explanation. Easy to understand. Thanks a lot…
Very good explanation. Easy to understand. Thanks a lot…
Hey
Its really nice post.
Well i have also posted an article on the same topic:
http://www.sqlservergeeks.com/articles/sql-server-bi/40/sql-server-rollup-and-cube-is-it-same
Is there any solution for problem with rollup that deals with no. of columns greater than 10 in group by clause.
Is there any solution for problem with rollup that deals with no. of columns greater than 10 in group by clause?
Plz reply.
@ sachin select the particular column in count function which is greater than 1 0
Thank you very much… i find this very useful and easy to understand!
gud work guyz….. really gud explanation…
Thanks, this explilation is mor help full to lern about cube’s and rollup.
Thanks,
Simple but effective explanation.
Thanks this query help me to learn about cube and rollup.
Really great example and clear information. Thank you
hi
very interesting artical i like this this is very simple to undestand thanks dude
what i want to know more about the cube command plz tell me more about the cube command…….?????????
gr8 article…ds s exactly what i need….Thanks.
Great its very nice.But i want to know more about ssas for analysing purpose can you help me.
If i have lakhs of records in order table and i want only output like row no(3,6 in above example) then it is possible or not
adding
for above i use fig(5)
thanks….good article
nice explanation guys …
Superb…..Expecting like these from you…
Great JOB….. 🙂
Great…. very very useful
nice one….
nice indeed..