Use of GROUPING SETS in SQL Server 2008 which could be replacement of CUBE and ROLLUP

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

Use of CUBE and ROLLUP – Difference of CUBE and ROLLUP in SQL Server 2005/2008

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