Question about WITH ROLLUP and HAVING...

• pazzy12

  SSC Enthusiast

  Points: 157

  More actions

  March 19, 2010 at 9:28 am

  #218064

  Hi

  I have a query that gets average min and max prices of a product .. it groups them by

  sub category and category.

  As you know WITH ROLLUP .. will then give an extra few rows with information on category only (or subcategory column ... (WITH CUBE does more then 1 column)) ..

  I notice when i add a HAVING filter at the end ... the ROLLUP is only done

  for 1 cat ??

  example to make clearer ...

  USE AdventureWorks2008R2

  SELECT PC.ProductCategoryID

  --,GROUPING(PC.ProductCategoryID)

  ,P.ProductSubcategoryID

  --,GROUPING(P.ProductSubcategoryID)

  ,AVG(listprice) as 'Average'

  , MIN(Listprice) as 'Minimum'

  , MAX(Listprice) as 'Maximum'

  FROM Production.Product as P

  JOIN Production.ProductSubCategory as SC

  ON SC.ProductSubcategoryID

  = P.ProductSubCategoryID

  JOIN Production.ProductCategory as PC

  ON SC.ProductCategoryID

  = PC.ProductCategoryID

  WHERE ListPrice <> 0

  GROUP BY PC.ProductCategoryID,P.ProductSubcategoryID

  WITH rollup

  HAVING MIN(ListPrice) > 200;

  Gives results ... :

  ProductCatIDProductSubcatIDAverageMinimumMaximum

  111683.365539.993399.99

  121597.45539.993578.27

  131425.2481742.352384.07

  1NULL1586.737539.993578.27

  212678.2535249.791364.50

  214780.0436337.221431.50

  216631.4155333.421003.91

  Now without the last line (HAVING....) ... it will do a rollup on ALL the ProductCatID ... not just one as above here....

  why would this be ?

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  March 19, 2010 at 9:39 am

  #1136837

  It's because MIN(ListPrice) for ProductCategoryID 2 < 200 in the rollup row. Try something like

  HAVING P.ProductSubcategoryID IS NULL OR MIN(ListPrice) > 200;