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.
The resulting
output will look like the figure given below.
Figure 1
