RSS
Feed
Tuesday, May 26, 2009 | 
I have used CUBE and ROLLUP in my last article at below given link:
http://www.sqlhub.com/2009/05/use-of-cube-and-rollup-difference-of.html
Those are really handy and very useful tools, especially for reporting purpose. SQL Server 2008 came up with more powerful utility called GROUPING SETS. GROUPING SETS is more user friendly and easy to use as compare with CUBE and ROLL UP. I will use same table and data I have used in my CUBE and ROLLUP article so that everybody can compare the changes.
Have a look at it.
--Table 1 for Demo
if object_id('dbo.orders','U') is not null drop table dbo.orders
GO
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
if object_id('dbo.OrderDetails','U') is not null drop table dbo.orderDetails
GO
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()
--1.)
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
--2.)
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 grouping sets ((o.OrderID,SampleDate))
/******************************************************************
there is not difference between query # 1 and 2
both will shows you results
OrderID SampleDate Total Sample
------- -------------------------------------------------- ------------
A1000 May 25 2009 1:56PM 1
A1000 May 26 2009 1:56PM 2
A1001 May 26 2009 1:56PM 2
(3 row(s) affected)
OrderID SampleDate Total Sample
------- -------------------------------------------------- ------------
A1000 May 25 2009 1:56PM 1
A1000 May 26 2009 1:56PM 2
A1001 May 26 2009 1:56PM 2
(3 row(s) affected)
*******************************************************************/
--now we will start real journey of Grouping Sets
--3.)
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 grouping sets ((o.OrderID,SampleDate),(o.OrderID))
/******************************************************************
above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID
You can see we are getting total sample of A1000 and A1001
OrderID SampleDate Total Sample
-------------------- -------------------------------------------------- ------------
A1000 May 25 2009 1:56PM 1
A1000 May 26 2009 1:56PM 2
A1000 Total Sample for OrdID 3
A1001 May 26 2009 1:56PM 2
A1001 Total Sample for OrdID 2
(5 row(s) affected)
*******************************************************************/
--now we need total on date rather than OrderID, let us see how it comes with below query.
--4.)
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 grouping sets ((o.OrderID,SampleDate),(SampleDate))
/******************************************************************
above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID
You can see we are getting total sample of A1000 and A1001
OrderID SampleDate Total Sample
-------------------- -------------------------------------------------- ------------
A1000 May 25 2009 1:56PM 1
Total Sample on Date May 25 2009 1:56PM 1
A1000 May 26 2009 1:56PM 2
A1001 May 26 2009 1:56PM 2
Total Sample on Date May 26 2009 1:56PM 4
(5 row(s) affected)
*******************************************************************/
--now we need total on date and total by OrderID, let us see how it comes with below query.
--5.)
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 grouping sets ((o.OrderID,SampleDate),(SampleDate),(o.OrderID))
/******************************************************************
above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID
and another aggregation will be applied to date also.
You can see we are getting total sample of A1000 and A1001 and total sample came on
25th and 26th May 2009
OrderID SampleDate Total Sample
-------------------- -------------------------------------------------- ------------
A1000 May 25 2009 1:56PM 1
Total Sample on Date May 25 2009 1:56PM 1
A1000 May 26 2009 1:56PM 2
A1001 May 26 2009 1:56PM 2
Total Sample on Date May 26 2009 1:56PM 4
A1000 Total Sample for OrdID 3
A1001 Total Sample for OrdID 2
(7 row(s) affected)
*******************************************************************/
--now, below query is exactly same like Query # 5 the only difference is
--we need grand total
--6.)
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 grouping sets ((o.OrderID,SampleDate),(SampleDate),(o.OrderID),())
/******************************************************************
above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID
and another aggregation will be applied to date also, apart from that () with no group by denots that
we need grand total also.
You can see we are getting total sample of A1000 and A1001 and total sample came on
25th and 26th May 2009 along with Total sample for all orderID so far, which is 5, which
is on 6th row.
OrderID SampleDate Total Sample
-------------------- -------------------------------------------------- ------------
A1000 May 25 2009 1:56PM 1
Total Sample on Date May 25 2009 1:56PM 1
A1000 May 26 2009 1:56PM 2
A1001 May 26 2009 1:56PM 2
Total Sample on Date May 26 2009 1:56PM 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)
*******************************************************************/
Happy Coding!!!
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