We all know when reporiting any financial or sales information adding sub totals and grand totals to report makes it more effective but most offten develop0rs dont realize TSQL provides a effective way in doing this is using rollup and cube operators in group by clause.
So for this demo I am using Adventure works 2008 OLTP sample database
Three tables
- Production.Product
- Production.ProductCategory
- Production.ProductSubcategory
here is a simple querying joining three tables and grouping by product category and subcategory
select
pc.ProductCategoryID,
s.ProductSubcategoryID,
AVG(ListPrice)as Avglistprice,
MAX (ListPrice)as MAxlistprice,
MAX(StandardCost)as Maxstandardcost
from Production.Product p
inner join Production.ProductSubcategory s
on p.ProductSubcategoryID = s.ProductSubcategoryID
inner join Production.ProductCategory pc
on pc.ProductCategoryID = s.ProductCategoryID
group by pc.ProductCategoryID,s.ProductSubcategoryID
SAMPLE RESULT
You can see we are missing some things in the above result i.e subtotals and grand totals
change group by cluase to include rollup operator like this
group by ROLLUP(pc.ProductCategoryID,s.ProductSubcategoryID)
trying running it with out errors
Result looks like this
CUBE is used as same way but it gives us a little different result , CUBE summarizes all the combinations of columns in group by clause and it also gives us grandtotal which does not make sense for above example because each sub category exists once in each category.
Sqljunkieshare 