Can You Compute?
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/canyoucomput
-
Thanks!
-
Wow, what a thorough explaination! I was tring to explain these concepts to some colleagues over lunch the other day, and your article beats the snot out of my back-of-napkin diagrams!
Thanks!
-
Very nice. The graphics you took the time to do the "Alice's Restaurant" thing to really make this a very clear article on what Rollups and Cubes actually do. The graphics clearly demonstrate why you might want to use the Grouping function of a Rollup or Cube.
There's lot's of folks who have no idea what a "subtree cost" is, how to determine it, or what unit of measurement it is. Also, although very obvious from the graphics, simple example Selects for the isolation of subtotals and grandtotals would be very helpful for newbies. A very brief explanation of these two things would have made this a "six star" article.
Outstanding article overall! Thanks for sharing it with us.
--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) -
You really did do a nice job on this article. Very complete description and nice use of visualizations to send home.
-
Excellent article. I had heard of the terms before and had "one day" planned to read up on them thinking they sounded complicated. You made it sound easy - and as it turns out, it really is
THANKS
-
I liked it very much. I would like to see more articles like this on the near future. My only "negative"
comment is I was reading the article and "copy and paste" the source code and it has a lot typos, here is the code will run:SELECT
Cust.country ,
Cat.CategoryName ,
Count(*) AS ProductsBought
FROM dbo.Customers AS Cust
INNER JOIN dbo.Orders AS Ord
ON Cust.CustomerId = Ord.CustomerId
INNER JOIN dbo.[Order Details] AS OrdDet
ON Ord.OrderId = OrdDet.OrderId
INNER JOIN dbo.Products AS Prod
ON OrdDet.ProductId = Prod.ProductId
INNER JOIN dbo.Categories AS Cat
ON Prod.CategoryId = Cat.CategoryId
GROUP BY
Cust.Country ,
Cat.CategoryName
WITH ROLLUP;
SELECT Cust.country ,
Cat.CategoryName ,
Count(*) AS ProductsBought
FROM dbo.Customers AS Cust
INNER JOIN dbo.Orders AS Ord
ON Cust.CustomerId = Ord.CustomerId
INNER JOIN dbo.[Order Details] AS OrdDet
ON Ord.OrderId = OrdDet.OrderId
INNER JOIN dbo.Products AS Prod
ON OrdDet.ProductId = Prod.ProductId
INNER JOIN dbo.Categories AS Cat
ON Prod.CategoryId = Cat.CategoryId
GROUP BY
Cust.Country ,
Cat.CategoryName
order by Cust.country;
GROUP BY clause.
SELECT
Cust.country ,
Cat.CategoryName ,
Count(*) AS ProductsBought,
GROUPING(Cust.country) AS SubtotalForCountries,
GROUPING(Cat.CategoryName) AS SubtotalForCategories
FROM dbo.Customers AS Cust
INNER JOIN dbo.Orders AS Ord
ON Cust.CustomerId = Ord.CustomerId
INNER JOIN dbo.[Order Details] AS OrdDet
ON Ord.OrderId = OrdDet.OrderId
INNER JOIN dbo.Products AS Prod
ON OrdDet.ProductId = Prod.ProductId
INNER JOIN dbo.Categories AS Cat
ON Prod.CategoryId = Cat.CategoryId
GROUP BY
Cust.Country ,
Cat.CategoryName
WITH CUBE ;
SELECT
Prod.ProductId ,
Prod.ProductName ,
Cat.CategoryName ,
Prod.UnitsInStock * Prod.UnitPrice AS StockValue
FROM dbo.Products AS Prod
INNER JOIN dbo.Categories AS Cat
ON Prod.CategoryId = Cat.CategoryId
ORDER BY
Cat.CategoryName
COMPUTE SUM(Prod.UnitsInStock * Prod.UnitPrice);
SELECT
Prod.ProductId ,
Prod.ProductName ,
Cat.CategoryName ,
Prod.UnitsInStock * Prod.UnitPrice AS StockValue
FROM dbo.Products AS Prod
INNER JOIN dbo.Categories AS Cat
ON Prod.CategoryId = Cat.CategoryId
ORDER BY
Cat.CategoryName
COMPUTE SUM(Prod.UnitsInStock * Prod.UnitPrice)
BY Cat.CategoryName;
SELECT
Prod.ProductId ,
Prod.ProductName ,
Cat.CategoryName ,
Prod.UnitsInStock * Prod.UnitPrice AS StockValue
FROM dbo.Products AS Prod
INNER JOIN dbo.Categories AS Cat
ON Prod.CategoryId = Cat.CategoryId
ORDER BY Cat.CategoryName
COMPUTE SUM(Prod.UnitsInStock * Prod.UnitPrice)
BY Cat.CategoryName
COMPUTE SUM(Prod.UnitsInStock * Prod.UnitPrice);
One more time.... Great article.
-
A very good explaination!
Kindest Regards,Al Pagcaliwangan B.S. ECE, B.S.EE
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply