Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Can You Compute? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, December 31, 2004 12:50 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 3:00 AM Points: 2,816, Visits: 1,593
Post #153180
 Posted Tuesday, January 11, 2005 4:47 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 10, 2012 6:17 AM Points: 10, Visits: 55
 Thanks!
Post #154740
 Posted Tuesday, January 11, 2005 5:44 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, June 13, 2013 11:14 PM Points: 11, Visits: 59
 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!
Post #154751
 Posted Tuesday, January 11, 2005 6:37 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 10:49 AM Points: 34,574, Visits: 28,757
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #154766
 Posted Wednesday, January 12, 2005 6:43 AM
 SSCrazy Eights Group: Moderators Last Login: Friday, December 06, 2013 9:56 AM Points: 8,369, Visits: 729
 You really did do a nice job on this article. Very complete description and nice use of visualizations to send home.
Post #155033
 Posted Thursday, January 13, 2005 3:51 PM
 Ten Centuries Group: General Forum Members Last Login: Sunday, September 16, 2012 3:26 AM Points: 1,038, Visits: 443
 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
Post #155498
 Posted Saturday, June 18, 2005 10:49 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, February 18, 2008 4:08 PM Points: 2, Visits: 3
 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.CategoryNameorder 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.
Post #191924
 Posted Wednesday, November 23, 2005 1:40 AM
 SSC Rookie Group: General Forum Members Last Login: Thursday, July 06, 2006 5:13 AM Points: 28, Visits: 1
 A very good explaination!    Kindest Regards,Al Pagcaliwangan B.S. ECE, B.S.EE
Post #239081

 Permissions