SQL Server 2008 Grouping Sets

Apr

SQL Server 2008 Grouping Sets

Written by Donna Posted in: SQL Server

The GROUPING SETS function allows you to choose whether to see subtotals and grand totals in your result set.

The GROUPING SET function allows you the versatility to:

include just one total
include different levels of subtotal
include a grand total
choose the position of the grand total in the result set

This first example shows how to generate only the totals for each Product by year and by Category. This is done in the GROUP BY clause.

Using the AdventureWorks database, let’s create a SELECT statement that will show totals by:

year
category
product name

Using the new GROUPING SETS function SQL sorts the fields from left to right and uses the last field in each set to create the totals. Notice the last line of the query.

SELECT YEAR(sh.OrderDate) AS Year, pc.name AS Categories, p.name AS Products
FROM Production.Product p
JOIN Production.ProductSubCategory ps
ON p.ProductSubCategoryID = ps.ProductSubCategoryID
JOIN Production.ProductCategory pc
ON ps.ProductCategoryID = pc.ProductCategoryID
JOIN Sales.SalesOrderDetail s
ON p.Productid = s.ProductID
JOIN Sales.SalesOrderHeader sh
ON s.SalesOrderID = sh.SalesOrderID
GROUP BY GROUPING SETS((Year(sh.OrderDate),pc.Name,p.Name);

This will return totals for each Product sorted by Year and Category. No grand total is included by default:

Results from using new GROUPING SETS function in SQL 2008 first level

Totals for each product

Adding a second set to the GROUP BY clause will create totals for each subcategory such as All_Purpose Bike Stand. Again, notice the last line of the query.

SELECT YEAR(sh.OrderDate) AS Year, pc.name AS Categories, p.name AS  Products
FROM Production.Product p
JOIN  Production.ProductSubCategory ps
ON p.ProductSubCategoryID =  ps.ProductSubCategoryID
JOIN Production.ProductCategory pc
ON  ps.ProductCategoryID = pc.ProductCategoryID
JOIN  Sales.SalesOrderDetail s
ON p.Productid = s.ProductID
JOIN  Sales.SalesOrderHeader sh
ON s.SalesOrderID = sh.SalesOrderID
GROUP BY GROUPING SETS((Year(sh.OrderDate),pc.Name,p.Name),(pc.name,p.name));

grouping set 2nd order - by category and product name

Totals by product

The next example will add totals to the GROUP BY clause for each category. No grand total is included by default. Once again, notice the last line of the query.

SELECT YEAR(sh.OrderDate) AS Year, pc.name AS Categories, p.name AS  Products
FROM Production.Product p
JOIN  Production.ProductSubCategory ps
ON p.ProductSubCategoryID =  ps.ProductSubCategoryID
JOIN Production.ProductCategory pc
ON  ps.ProductCategoryID = pc.ProductCategoryID
JOIN  Sales.SalesOrderDetail s
ON p.Productid = s.ProductID
JOIN  Sales.SalesOrderHeader sh
ON s.SalesOrderID = sh.SalesOrderId
GROUP BY GROUPING SETS((Year(sh.OrderDate),pc.Name,p.Name),(pc.name,p.name),(p.name);

Adding Category Totals to report

The final step is to add a grand total to this report. This is done by using “()” in the grouping set.

SELECT YEAR(sh.OrderDate) AS Year, pc.name AS Categories, p.name AS   Products
FROM Production.Product p
JOIN  Production.ProductSubCategory ps
ON p.ProductSubCategoryID =  ps.ProductSubCategoryID
JOIN Production.ProductCategory pc
ON  ps.ProductCategoryID = pc.ProductCategoryID
JOIN  Sales.SalesOrderDetail s
ON p.Productid = s.ProductID
JOIN  Sales.SalesOrderHeader sh
ON s.SalesOrderID = sh.SalesOrderId
GROUP BY GROUPING SETS((Year(sh.OrderDate),pc.Name,p.Name),(pc.name,p.name),(p.name),());

Grand Total

To place the Grand Total at the top of the report, change to order of the grouping sets list and place the parenthesis at the beginning of the list:

11	GROUP BY GROUPING SETS((),(Year(sh.OrderDate),pc.Name,p.Name),(pc.name,p.name),(p.name));

In this example, we showed how to use the new GROUPING SETS to create a report that includes different levels of totals including explicitly adding the Grand Total.

GROUPING SETS are covered in more detail in our Writing Queries Using Microsoft SQL Server 2008 Transact-SQL class.

No TweetBacks yet. (Be the first to Tweet this post)