GROUP BY, GROUPING SETS, ROLLUP and CUBE
Before you I go directly with each function I can give you table view where I have tested all these functions.
I have used two tables to create an example with order and purchasing items for same.
GROUP BY and GROUPING SETS
I do not need to explain much about GROUP BY since everybody knows well but when we combine GROUPING SETS with GROUP BY it is quiet different and gives a summary result on top of GROUP BY. So basically if some body want to find a total amount at each order or purchase level, this will be the ideal function. Please see below the query and results
SELECT oh.OrderID ,oh.ProductID ,SUM(p.price) AS Total FROM OrderHistory oh inner join product p on oh.productid = p.productid GROUP BY GROUPING SETS(oh.OrderID,oh.ProductID)
ROLLUP
ROLLUP is again going one more level into the summary . for eg : if we want to display Order and items purchased for that order and also total order.
SELECT COALESCE(cast(oh.OrderID as varchar(50)),'Grand : ') as 'Order', COALESCE(p.Name,'Total : ') as Product ,SUM(p.price) AS Total FROM OrderHistory oh inner join product p on oh.productid = p.productid GROUP BY oh.OrderID,p.Name WITH ROLLUP
CUBE
CUBE is again going one more level into the summary . for eg : if we want to display Order and each items purchased for that order and also total order.
SELECT oh.OrderID as ‘Order’, COALESCE(p.Name,‘Total : ‘) as Product, SUM(p.price) AS Total FROM OrderHistory oh inner join product p on oh.productid =p.productid GROUP BY oh.OrderID, p.Name WITH CUBE
happy reading and please let us know if anybody have a better approach than this or any other easy and optimized way to achieve the same summary results.
pulee
reneeshprabha
January 16, 2013 at 10:22 am