Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can You Compute? Expand / Collapse
Author
Message
Posted Friday, December 31, 2004 12:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 2,868, Visits: 1,711
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/canyoucomput

LinkedIn Profile
Newbie on www.simple-talk.com
Post #153180
Posted Tuesday, January 11, 2005 4:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 8:18 AM
Points: 10, Visits: 56

Thanks!

Post #154740
Posted Tuesday, January 11, 2005 5:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302

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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #154766
Posted Wednesday, January 12, 2005 6:43 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 1:22 PM
Points: 8,369, Visits: 734
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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

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




Post #191924
Posted Wednesday, November 23, 2005 1:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse