Using grouping sets in sql server 2008 tech republic

Using Grouping Sets in SQL Server 2008 | TechRepublic

ZDNet Asia SmartPlanet TechRepublic Log In Join TechRepublic FAQ Go Pro!

Blogs Downloads Newsletters Galleries Q&A Discussions News
Research Library

IT Management Development IT Support Data Center Networks Security

Home / Blogs / The Enterprise Cloud Follow this blog:

The Enterprise Cloud

Using Grouping Sets in SQL
Server 2008
By Tim Chapman
October 3, 2008, 8:23 AM PDT

Takeaway: A neat new feature in SQL Server 2008 is the GROUPING SETS clause, which
allows you to easily specify combinations of field groupings in your queries to see different levels of
aggregated data. Today we’ll look at how you can use the new SQL Server 2008 GROUPING
SETS clause to aggregate your data.

GROUP BY
The GROUP BY clause is a SQL language construct used to priovide summary data for column
returned in a SELECT statement. This functionality groups values from specified fields together,
providing a single record of distinct values for each group. To illustrate the how GROUP BY
works, lets look at an example. Use the script below to create and load the SalesHistory table.

IF OBJECT_ID('SalesHistory')>0

DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory]

( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Product] [char] (150) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)

GO

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=100)

BEGIN INSERT INTO SalesHistory

(Product, SaleDate, SalePrice)

VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO SalesHistory


http://www.techrepublic.com/blog/datacenter/using-grouping-sets-in-sql-server-2008/456[08/29/2012 3:43:37 PM]


VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO SalesHistory


VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1

END

I can use the GROUP by clause to retrieve the average SalePrice per Product.

SELECT Product, AVG(SalePrice) AS AverageSalePrice

FROM SalesHistory

GROUP BY Product

Notice that the GROUP BY clause returns a distinct Product list, followed by the average of each
SalePrice value.

The only drawback of the GROUP BY clause is that it only really allows you to specify
aggregations for a single set of database columns. The new SQL Server 2008 GROUPING SETS
clause expands upon the GROUP BY functionality allowing you to specify different field
combinations to return aggregate data.

GROUPING SETS

This functionality can enhance reporting requirements and data analysis by allowing you to retrieve
aggregation data through one statement, rather than several distinct queries. GROUPING SETS
also allows for “Grand total” data for the entire set of data, or just for sections of aggregations.

The following query uses the GROUPING SETS clause on the SalesHistory table. This query
returns 2 different sets of aggregations; one at the Product level, and a “Grand total” level, denoted
by the () in the GROUPING SETS clause.

SELECT Product, SUM(SalePrice) AS TotalSales, COUNT(*) AS SaleCount

FROM SalesHistory

GROUP BY GROUPING SETS((Product),())

ORDER BY Product DESC, TotalSales DESC, SaleCount DESC

Notice the last row returned in the above screenshot. This is the “Grant total” record I mentioned
above.

In the following query, I use three separate field sets in my GROUPING SETS clause. Doing so
create three totally different sets of aggregations; one by Product, one by the SalesTier (calculated
via a subquery), and one “Grand total” column. Note that the order in which the grouping sets
appear in the GROUPING SETS clause is not important.

SELECT

Product, SalesTier, TotalSales = SUM(SalePrice), AverageSalePrice = AVG(SalePrice)

FROM (

SELECT

Product,

SalesTier =

CASE WHEN SalePrice BETWEEN 0 AND 500 THEN 'Tier 1'

WHEN SalePrice BETWEEN 501 AND 750 THEN 'Tier 2'


WHEN SalePrice > 1000 THEN 'Tier 4'

END,

SalePrice

FROM SalesHistory

) a

GROUP BY



GROUPING SETS((Product), (SalesTier), ())

ORDER BY Product DESC, SalesTier DESC

You can achieve the same results as the above query using typical TSQL constructs, but it is
much more cumbersome to do so, as I show in the following script.

IF OBJECT_ID('tempdb..#SalesResults') IS NOT NULL

DROP TABLE #SalesResults

CREATE TABLE #SalesResults

(

Product VARCHAR(10),

SalesTier
VARCHAR(10),
Next Time

TotalSales MONEY,

AverageSalePrice MONEY

)

INSERT INTO #SalesResults(Product, SalesTier, TotalSales, AverageSalePrice)

SELECT

Product, NULL, SUM(SalePrice), AVG(SalePrice)

FROM

SalesHistory

GROUP BY Product

UNION ALL

SELECT

NULL,

SalesTier,

SUM(SalePrice),

AVG(SalePrice)

FROM

(

SELECT

SalesTier =

CASE




WHEN SalePrice > 1000 THEN 'Tier 4'

END,

SalePrice

FROM SalesHistory

) a

GROUP BY SalesTier

UNION ALL

SELECT

NULL, NULL, SUM(SalePrice), AVG(SalePrice)

FROM SalesHistory

SELECT *

FROM #SalesResults

Next Time



Today I took a look at the usefulness of the new GROUPING SETS clause in SQL Server 2008.
The results of the GROUPING SETS clause can be achived in other ways, but it takes more
programming work to do so. Next time I will take a look at SQL Server 2008 enhancements to the
CUBE and ROLLUP; two constructs that can produce similar results to the GROUPING SETS
clause, but in a different manner.

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free
newsletters.

About Tim Chapman
Full Bio Contact

Take a stance on virtual Will you evaluate Microsoft
machine time sync Hyper-V Server 2008?

2 Join the conversation! Add Your Opinion
Comments Follow via:

Staff Picks Top Rated Most Recent My Contacts See All Comments

RE: Using Grouping Sets in SQL Server 2008 0
chapman.tim@... 7th Oct 2008 Votes

Looks to be...thanks for noticing. I'll get with TR to get them posted.

View in thread

Missing screen shots? 0
MikeSQLDBA 6th Oct 2008 Votes

Interesting article, but are the screen shots missing?

View in thread

See all comments

Join the TechRepublic Community and join the conversation! Signing-up is
free and quick, Do it now, we want to hear your opinion.

Join Login


Using grouping sets in sql server 2008 tech republic

More Related Content

Viewers also liked

Similar to Using grouping sets in sql server 2008 tech republic

More from Kaing Menglieng

Using grouping sets in sql server 2008 tech republic