using grouping sets in sql server 2008 tech republic
TRANSCRIPT
-
Using Grouping Sets in SQL Server 2008 | TechRepublic
http://www.techrepublic.com/blog/datacenter/using-grouping-sets-in-sql-server-2008/456[08/29/2012 3:43:37 PM]
Blogs Downloads Newsletters Galleries Q&A Discussions News
Research Library
Home / Blogs / The Enterprise Cloud
The Enterprise Cloud
Using Grouping Sets in SQLServer 2008By Tim ChapmanOctober 3, 2008, 8:23 AM PDT
Takeaway: A neat new feature in SQL Server 2008 is the GROUPING SETS clause, whichallows you to easily specify combinations of field groupings in your queries to see different levels ofaggregated data. Today well look at how you can use the new SQL Server 2008 GROUPINGSETS clause to aggregate your data.
GROUP BYThe GROUP BY clause is a SQL language construct used to priovide summary data for columnreturned 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 BYworks, 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
-
Using Grouping Sets in SQL Server 2008 | TechRepublic
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
(Product, SaleDate, SalePrice)
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 eachSalePrice value.
The only drawback of the GROUP BY clause is that it only really allows you to specifyaggregations for a single set of database columns. The new SQL Server 2008 GROUPING SETSclause expands upon the GROUP BY functionality allowing you to specify different fieldcombinations to return aggregate data.
GROUPING SETS
This functionality can enhance reporting requirements and data analysis by allowing you to retrieveaggregation data through one statement, rather than several distinct queries. GROUPING SETSalso 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 queryreturns 2 different sets of aggregations; one at the Product level, and a Grand total level, denotedby 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 mentionedabove.
In the following query, I use three separate field sets in my GROUPING SETS clause. Doing socreate three totally different sets of aggregations; one by Product, one by the SalesTier (calculatedvia a subquery), and one Grand total column. Note that the order in which the grouping setsappear 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 BETWEEN 751 AND 1000 THEN 'Tier 3'
WHEN SalePrice > 1000 THEN 'Tier 4'
END,
SalePrice
FROM SalesHistory
) a
GROUP BY
-
Using Grouping Sets in SQL Server 2008 | TechRepublic
http://www.techrepublic.com/blog/datacenter/using-grouping-sets-in-sql-server-2008/456[08/29/2012 3:43:37 PM]
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 ismuch 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),
SalesTierVARCHAR(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 BETWEEN 0 AND 500 THEN 'Tier 1'
WHEN SalePrice BETWEEN 501 AND 750 THEN 'Tier 2'
WHEN SalePrice BETWEEN 751 AND 1000 THEN 'Tier 3'
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
-
Using Grouping Sets in SQL Server 2008 | TechRepublic
http://www.techrepublic.com/blog/datacenter/using-grouping-sets-in-sql-server-2008/456[08/29/2012 3:43:37 PM]
2Comments
Add Your Opinion
See All CommentsMy Contacts
Join Login
Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.
About Tim Chapman
See all comments
Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.
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 moreprogramming work to do so. Next time I will take a look at SQL Server 2008 enhancements to theCUBE and ROLLUP; two constructs that can produce similar results to the GROUPING SETSclause, but in a different manner.
Join the conversation!
Full Bio Contact
Take a stance on virtualmachine time sync
Will you evaluate MicrosoftHyper-V Server 2008?
Follow via:
Staff Picks Top Rated Most Recent
View in thread
RE: Using Grouping Sets in SQL Server 2008chapman.tim@... 7th Oct 2008
Looks to be...thanks for noticing. I'll get with TR to get them posted.
0Votes
View in thread
Missing screen shots?MikeSQLDBA 6th Oct 2008
Interesting article, but are the screen shots missing?
0Votes
http://www.techrepublic.com/forum/discussions/102-275545http://www.techrepublic.com/forum/discussions/102-275545http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/forum/discussions/102-275545http://www.techrepublic.com/blog/datacenter/take-a-stance-on-virtual-machine-time-sync/450http://www.techrepublic.com/blog/datacenter/take-a-stance-on-virtual-machine-time-sync/450http://www.techrepublic.com/blog/datacenter/take-a-stance-on-virtual-machine-time-sync/450http://www.techrepublic.com/blog/datacenter/will-you-evaluate-microsoft-hyper-v-server-2008/457http://www.techrepublic.com/blog/datacenter/will-you-evaluate-microsoft-hyper-v-server-2008/457http://www.techrepublic.com/blog/datacenter/will-you-evaluate-microsoft-hyper-v-server-2008/457http://www.techrepublic.com/forum/discussions/102-275545/rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fforum%2Fdiscussions%2F102-275545%2Frss&title=Discussion+on+Using+Grouping+Sets+in+SQL+Server+2008+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fusing-grouping-sets-in-sql-server-2008%2F456&frequency=weekly&rss_type=42http://www.techrepublic.com/forum/discussions/102-275545-2611643http://www.techrepublic.com/members/profile/4492495http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/forum/discussions/102-275545-2610925http://www.techrepublic.com/members/profile/3080662http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/vote
techrepublic.comUsing Grouping Sets in SQL Server 2008 | TechRepublic
luLXNxbC1zZXJ2ZXItMjAwOC80NTYA: form1: q: button3: