SQL Server 2005+ came up with exciting facility of CUBE and ROLLUP clause. Herewith, I am going to show you use and difference of CUBE and ROLLUP in SQL Server 2005 and SQL Server 2008.
CUBE: generates a result set that represents aggregates for all combinations of values in the selected columns
ROLLUP: generates a result set that represents aggregates for a hierarchy of values in the selected columns
Let us see one practical scenario which will make your concept much clear about both these exciting features.
--Table 1 for Demo
create table dbo.Orders
(
OrderID varchar(5),
OrderDate varchar(50)
)
--date for table1
insert into dbo.Orders
select 'A1000',GETDATE()-1 union all
select 'A1001',GETDATE()
--table 2 for demo
create table dbo.OrderDetails
(
OrderID varchar(5),
SampleNo Varchar(8),
SampleDate varchar(50)
)
--data for table 2
insert into dbo.OrderDetails
select 'A1000','A1000-01',GETDATE()-1 union all
select 'A1000','A1000-02',GETDATE() union all
select 'A1000','A1000-03',GETDATE() union all
select 'A1001','A1001-01',GETDATE() union all
select 'A1001','A1001-02',GETDATE()
--let us check both table
select * from dbo.Orders
select * from dbo.OrderDetails
--let us check how many samples came for each order id
select
o.orderID,
count(od.SampleNo) as 'Total Sample'
from dbo.Orders o join dbo.OrderDetails od
on o.OrderID=od.OrderID
group by o.OrderID
/************************************************************
answer would be.
orderID Total Sample
------- ------------
A1000 3
A1001 2
(2 row(s) affected)
*************************************************************/
select
o.orderID,
count(od.SampleNo) as 'Total Sample'
from dbo.Orders o join dbo.OrderDetails od
on o.OrderID=od.OrderID
group by o.OrderID
with cube
/************************************************************
answer would be.
orderID Total Sample
------- ------------
A1000 3
A1001 2
NULL 5
(3 row(s) affected)
*************************************************************/
select o.orderID,
count(od.SampleNo) as 'Total Sample'
from dbo.Orders o join dbo.OrderDetails od
on o.OrderID=od.OrderID
group by o.OrderID
with rollup
/************************************************************
answer would be.
orderID Total Sample
------- ------------
A1000 3
A1001 2
NULL 5
(3 row(s) affected)
*************************************************************/
--You will not be able to find big difference between ROLLUP and CUBE with above query.
--isn't there any difference at all? NO, there is a difference. let us see the difference.
select
case when grouping(o.orderID)=1 THEN 'Total Sample on Date' else o.OrderID end as OrderID,
case when grouping(od.SampleDate)=1 THEN 'Total Sample for OrdID' else od.SampleDate end as SampleDate,
count(od.SampleNo) as 'Total Sample'
from dbo.Orders o join dbo.OrderDetails od
on o.OrderID=od.OrderID
group by o.OrderID,SampleDate
with cube
/************************************************************
answer would be.
OrderID SampleDate Total Sample
-------------------- -------------------------------------------------- ------------
A1000 May 24 2009 3:53PM 1
Total Sample on Date May 24 2009 3:53PM 1
A1000 May 25 2009 3:53PM 2
A1001 May 25 2009 3:53PM 2
Total Sample on Date May 25 2009 3:53PM 4
Total Sample on Date Total Sample for OrdID 5
A1000 Total Sample for OrdID 3
A1001 Total Sample for OrdID 2
(8 row(s) affected)
*************************************************************/
select
case when grouping(o.orderID)=1 THEN 'Total Sample on Date' else o.OrderID end as OrderID,
case when grouping(od.SampleDate)=1 THEN 'Total Sample for OrdID' else od.SampleDate end as SampleDate,
count(od.SampleNo) as 'Total Sample'
from dbo.Orders o join dbo.OrderDetails od
on o.OrderID=od.OrderID
group by o.OrderID,SampleDate
with rollup
/************************************************************
answer would be.
OrderID SampleDate Total Sample
-------------------- -------------------------------------------------- ------------
A1000 May 24 2009 3:53PM 1
A1000 May 25 2009 3:53PM 2
A1000 Total Sample for OrdID 3
A1001 May 25 2009 3:53PM 2
A1001 Total Sample for OrdID 2
Total Sample on Date Total Sample for OrdID 5
(6 row(s) affected)
*************************************************************/
In last two queries, you find the difference between CUBE and ROLLUP. Second from last query with CUBE shows you 8 rows while last query which is using ROLLUP, showing 6 rows. CUBE will show you sample received for each orderID on each day and finally it will show you total sample received whereas ROLLUP will show you bit less summary like: which day for which ORDERID, how many samples come in. and finally total sample received.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
0 comments:
Post a Comment