AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1716&pId=-1
SQL Server 2008 New Features - Grouping Sets
page
by Nidal Arabi
Feedback
Average Rating: 
Views (Total / Last 10 Days): 16530/ 27

Introduction

With its new release, Microsoft SQL Server 2008 included some new features that some of us (as developers) will find interesting and useful. In this article about the new features of SQL Server 2008, I will try to shed some light on the grouping Sets feature (a nifty addition) as attributed to the family of T-SQL.

About The Feature

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.

Requirements

In order to apply the article procedures, you should have installed any version of Microsoft SQL Server 2008 (Express, Standard, Developer, or Enterprise) and applied the first article of this series - SQL Server 2008 New Features - Row Constructors.

Step by Step Explanation

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] [intNOT 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  = ONON [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

Summary

The grouping sets feature is a very important addition since it does one pass and summarizes the data for you. Before, you had to make several passes before obtaining the results. Nowadays, you have one pass with multiple results. Don't you love Microsoft? See you in the next article of SQL Server 2008 features.



©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 6:08:47 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search