March 12, 2008 at 12:06 am
Hello All,
I am still focusing on GROUP BY and its related clauses. This one is exceptionally meaty to me. Here is the info:
When you supply the WITH CUBE clause as an argument to the GROUP BY clause, you add summary rows for every possible combination of groups and subgroups in the result set. The NULL in the column indicates that you are using summary rows.
Use Northwind
SELECT CategoryName, ProductName,
Avg(UnitsInStock)AS 'Average in Stock'
FROM Categories cat JOIN Products pro
ON pro.CategoryID = cat.CategoryID
WHERE ProductName LIKE 'G%'
GROUP BY CategoryName, ProductName WITH CUBE
My questions are:
1) What exactly is WITH CUBE in plainer terms? I have not found a simple explanation.
2) Isn't this query one that would affect performance unnecessarily?
Let me know what you know, thanks!
March 12, 2008 at 12:42 am
Does this help you? Cube and Rollup
Like many things in SQL, it probably has a performance impact, but if you need the summary rows it produces, then you need the rows.
As a brief summary, a normal group by counting fruit would return the following
Apples 5
Oranges 7
Pears 1
Bananas 2
A group by with cube would return the following
Apples 5
Oranges 7
Pears 1
Bananas 2
Total 15
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2008 at 8:47 pm
Hi Gail,
I had a chance to review the info you sent and on the link as well.
I can see how WITH CUBE and WITH ROLLUP.
I see that WITH ROLLUP adds summary rows for Colour column, sort of like an ongoing subtotal per category.
However, WITH CUBE adds summary rows for BOTH Colour and Model columns.
So, I am going to make an educated guess here and say that the reason WITH CUBE only adds summary rows for Colour is because it is the first column in the SELECT statement. And that WITH ROLLUP adds summary rows for EVERY column in the SELECT statement.
Am I correct?
March 14, 2008 at 11:41 am
I believe it has to do with the order of the columns in the group by, not the select. I don't have a SQL instance around right now to test on.
iirc, there's sample code at the end of that article, so try it out for yourself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply