Grouping Sets - In SQL Server 2008

The grouping sets feature allows the developer to group data selected

in the same select statement into different groups.

The grouping sets intended to provide a powerful grouping tool in a

single SQL Statement by different fields having the same selection

number of fields. This feature is especially useful in the case of

collecting summary data using different criteria.

Steps :

Follow the easy steps below to create your environment and work area.

1. Open Microsoft SQL Server Management Studio.

2. Right click the database DbWork and choose query window.

3. Use the listing below to create a customer address table that

would contain all the orders by a specific customer id.

Listing 1 - T-SQL to create the order table

CREATE TABLE [dbo].[TblCustomerAddress](
[CustomerId] [int] NOT NULL,
[CustomerContinent] [varchar](50) NOT NULL,
[CustomerCountry] [varchar](50) NOT NULL,
[CustomerCity] [varchar](50) NOT NULL,
CONSTRAINT [PK_TblCustomerAddress] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY

= OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

4. Use the following listing below in order to insert some records

in the order tables.

Listing 2 - T-SQL to insert data into the customer address table using

the row constructor method

Insert into TblCustomerAddress (CustomerId, CustomerContinent,

CustomerCountry,
CustomerCity)
Values (1,'America','USA','Florida'),
(2,'America','USA','Kansas'),
(3,'Asia','Lebanon','Florida'),
(4,'Asia','KSA','Riyad'),
(5,'Europe','France','Paris'),
(6,'Europe','Greate Britan','Paris')

5. Suppose your manager came in and said, "I want a count of our

customer addresses according to a count by continent, a count by

country, a count by city and a total count." In order to do this

before, you would have had to write a list of 4 queries as listed in

the example below.

Listing 3

--Count the customer by Continent
Select CustomerContinent, COUNT(*) From TblCustomerAddress
Group By CustomerContinent

--Count the customer by Country
Select CustomerCountry, COUNT(*) From TblCustomerAddress
Group By CustomerCountry

--Count the customer by City
Select CustomerCity, COUNT(*) From TblCustomerAddress
group By CustomerCity

-- Count All Customers
Select COUNT(*) From TblCustomer

6. How to solve the problem? SQL Server
2008 provided the grouping sets to do this. The grouping sets feature

allows having different grouping in the same SQL Statement
. The listing below shows the statement.

Listing 4

SELECT CustomerCity, CustomerCountry, CustomerContinent, Count(*) AS

Count
FROM TblCustomerAddress
GROUP BY GROUPING SETS (
(CustomerCity),
(CustomerCountry),
(CustomerContinent),
()
)
ORDER BY CustomerCity, CustomerCountry, CustomerContinent

7. This statement would return all results combined and ordered. The

first grouping is done by customer city, the second is by customer

country, the third one is by customer continent, while the last one is

for the total of the table. So there is no grouping by a field done

here.