Using Rollup

Question

Post reply

Using Rollup

Dhruvesh Shah

SSCrazy

Points: 2303
More actions
May 31, 2011 at 6:03 am

#240555

Hi All,
I am trying to understand how Rollup works. following query summarize
Use AdventureWorks
select Production.ProductCategory.ProductCategoryID
,GROUPING(Production.ProductCategory.ProductCategoryID)
, Production.Product.ProductSubcategoryID
,GROUPING(Production.Product.ProductSubcategoryID)
, AVG(Listprice) As 'Average'
, MIN(ListPrice) As 'Min'
, MAX(ListPrice) As 'Max'
from Production.Product
join Production.ProductSubcategory
on production.ProductSubcategory.ProductSubcategoryID =
production.Product.ProductSubcategoryID
join Production.ProductCategory
on production.ProductSubcategory.ProductCategoryID =
production.ProductCategory.ProductCategoryID
where ListPrice <> 0
group by (production.ProductCategory.ProductCategoryID), (production.Product.ProductSubcategoryID)
with rollup
having MIN(ListPrice) > 200
produces following result
ProductCategoryID(No column name)ProductSubcategoryID(No column name)AverageMinMax
10101683.365539.993399.99
10201597.45539.993578.27
10301425.2481742.352384.07
10NULL11586.737539.993578.27
20120678.2535249.791364.50
20140780.0436337.221431.50
20160631.4155333.421003.91
My Question is why it doesn't summarize where productCategoryID is 2?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 1

Comment out HAVING clause and if that doesn't help, comment-out WHERE also.

Please use aliases, e.g:

SELECT mt.Name

from myDatabase.mySchema.MyTable mt -- mt is alias from this long full table name

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Sorry... didn't read the post correctly and withdrew my post.

--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)
Intro to Tally Tables and Functions