Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Grouping Sets: Good to Know Feature TIP# 60

DZone's Guide to

Grouping Sets: Good to Know Feature TIP# 60

· Java Zone ·
Free Resource

Get the Edge with a Professional Java IDE. 30-day free trial.

Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution.

Problem:-  Suppose , We want  an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination.

So, to resolve this problem a basic traditional way is to create 3 separate query and combine there result.

Solution:-  Now in SQL Server 2008 onwards we have a new feature for to achieve such problem which is called GROUPING SETS.

Lets understand this by an example.

I am taking here Adventureworks2012 database. Now we want total due amount on different basis  example

1) total due amount on Territory name and sales person basis

2) Total due amount on  Territory name

3) total due amount on sales person basis

4) total due amount on sales order date  basis

To achieve above  results we write following query

SELECT sod.OrderDate,
st.Name,
p.LastName + ‘,’+ p.FirstName  As SalesMan,
SUM(sod.TotalDue) as totalDue
FROM [Sales].[SalesOrderHeader] sod
INNER JOIN [Sales].[SalesPerson] sp ON sp.BusinessEntityID = sod.SalesPersonID
INNER JOIN [HumanResources].[Employee] emp ON emp.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [Person].[Person] p ON p.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [sales].SalesTerritory st ON st.TerritoryID = sod.TerritoryID
GROUP BY GROUPING SETS (
  (st.Name,p.LastName + ‘,’ +p.FirstName ),
  (st.Name),
  (p.LastName + ‘,’ +p.FirstName ),
  (sod.OrderDate)
 
)
ORDER BY  st.Name,sod.OrderDate 

Now when we run the query and we get results which we want.

GroupSetResult

I hope this may be help you some where.

Thanks !!!

RJ!!!


Get the Java IDE that understands code & makes developing enjoyable. Level up your code with IntelliJ IDEA. Download the free trial.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}