Grouping Sets in SQL Server 2008

Description: In this article I will describe grouping sets in SQL Server 2008 and the advantage of grouping sets compared to a simple Group by clause.

Content:

A new feature introduced in SQL Server 2008 is the GROUPING SETS clause, which allows us to easily specify combinations of field groupings in our queries to see different levels of aggregated data.

The GROUPING SETS function allows us to choose whether to see subtotals and grand totals in the table result set.

Now I am writing some query to introduce grouping sets in SQL Server 2008 and also discuss why this feature was introduced in SQL Server 2008.

Step 1:

Open SQL Server 2008 edition.

Step 2:

Open a new query and select any of your existing databases and create a table with the 3 columns:

1. Customerid
2. Year
3. Sales

createtable Customer(customerid int,yearint, sales money)

Step 3:

Now I am feeding the data into the table:

insertinto customer values
(1, 2005, 12000),
(1, 2006, 18000),
(1, 2007, 25000),
(2, 2005, 15000),
(2, 2006, 6000),
(3, 2006, 20000),
(3, 2007, 24000)

See, in SQL Server 2008 there is no need to write separate queries for insert. You can directly insert here your data by giving the "," operator.

Step 4:

Now run the below queries; it will look as in Figure 1:

select*from dbo.Customer

Figure 1:



Now you want to group by or filter this table with customerid, year, and sum of sales and have to combine all the records and have to display it into a single record format.

Step 5:

Now Before SQL Server 2008 using Group By and Union All you can achieve this. The queries are:

select customerid,null [year],sum(sales) [total sales]
from Customer
groupby customerid
unionall
selectnull [customerid], [year],sum(sales) [total sales]
from Customer
groupby [year]
unionall
selectnull [customerid],null [year],sum(sales) [total sales]
from Customer

See in the first query we group by the customer id. In the second one we group by the year and in each query we have done the "unionall" operation to combine all the records and display it.

Step 6:

Now run the above script; it will look like as in Figure 2:

Figure 2:



See the above result; show us the grouping format.

The first one is the group based on customer id marked as red and the second one is the group based on year which I marked with blue.

Step 7:

Now With SQL Server 2008, using Grouping Sets we can write the abobe query as:

select customerid, [year],sum(sales) [total sales]
from Customer
groupbygroupingsets ((customerid),(year),())

See, instead of writing the group by clause and the union all in each query, here we are using the grouping sets method and pass the parameter that I want to group. Like customerid and year.

groupingsets ((customerid),(year),())

Now I am writing the whole script here:

--table creation
ifobject_id('Customer','U')isnotnulldroptable Customer;
go
createtable Customer(customerid int,yearint, sales money)

--data insertion
insertinto customer values
(1, 2005, 12000),
(1, 2006, 18000),
(1, 2007, 25000),
(2, 2005, 15000),
(2, 2006, 6000),
(3, 2006, 20000),
(3, 2007, 24000)
select*from dbo.Customer
select customerid,null [year],sum(sales) [total sales]
from Customer
groupby customerid
unionall
selectnull [customerid], [year],sum(sales) [total sales]
from Customergroupby [year]
unionall
selectnull [customerid],null [year],sum(sales) [total sales]
from Customer

--With SQL Server 2008, using Grouping Sets
select customerid, [year],sum(sales) [total sales]
from Customer
groupbygroupingsets ((customerid),(year),())

Conclusion:

So in this article we have seen how to use grouping sets in SQL Server 2008.