jump to navigation

SQL Server 2008 new features-Grouping sets March 6, 2008

Posted by fofo in SQL Server 2008.
Tags: , ,
trackback

In this post i will try to introduce a new T-SQL enhancement,Grouping sets, that will be available with SQL Server 2008.

In order to follow this example you need to open SQL Server Management Studio.Then you need to open a query window and write the following sql statement that creates a brand new table.

CREATE TABLE [dbo].[ProductSales](

[ID] [int] IDENTITY(1,1) NOT NULL,

[City] [varchar](30) NOT NULL,

[Province] [varchar](30) NOT NULL,

[Amount] [money] NOT NULL,

CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]

then we enter some data to the new table with an insert statement that enters multiple rows at once

INSERT INTO ProductSales (City, Province, Amount)

VALUES (‘Athens’, ‘St-Ellada’, ‘125.00’),

(‘Bolos’, ‘Thessalia’, ‘120.00’),

(‘Patras’, ‘Peloponisos’, ‘55.00’),

(‘Athens’, ‘St-Ellada’, ‘425.00’),

(‘Larissa’, ‘Thessalia’, ‘150.00’),

(‘Piraeus’, ‘St-Ellada’, ‘265.00’),

(‘Aigio’, ‘Peloponisos’, ‘107.00’),

(‘Salonica’, ‘Makedonia’, ‘537.00’),

(‘Larissa’, ‘Thessalia’, ‘557.00’),

(‘Piraeus’, ‘St-Ellada’, ‘285.00’),

(‘Athens’, ‘St. Ellada’, ‘710.00’),

(‘Kavala’, ‘Makedonia’, ‘527.00’),

(‘Salonica’, ‘Makedonia’, ‘127.00’),

(‘Bolos’, ‘Thessalia’, ‘120.00’),

(‘Patras’, ‘Peloponisos’, ‘155.00’),

(‘Athens’, ‘St-Ellada’, ‘425.00’),

(‘Kavala’, ‘Makedonia’, ‘527.00’),

(‘Larissa’, ‘Thessalia’, ‘540.00’),

(‘Piraeus’, ‘St-Ellada’, ‘565.00’),

(‘Aigio’, ‘Peloponisos’, ‘107.00’)

now we want to create a group statement to group data by city

SELECT City, Sum(Amount) AS Amount

FROM ProductSales

GROUP BY City

when we execute the statement we see the total amount taken by each city.

similarly we can group by province.

SELECT Province, Sum(Amount) AS Amount

FROM ProductSales

GROUP BY Province

when we execute the statement we see the total amount taken by each province.

if we want to find the total amount taken we simply type in the query window

SELECT Sum(Amount) AS Amount

FROM ProductSales

So far i have created 3 different statements to get the results i wanted. wouldn’t be great to combine all the 3 above mentioned statements into one?

i can do that in sql server 2008 using grouping sets.so for the same example i have been using all along we have:

SELECT City, Province, Sum(Amount) AS Amount

FROM ProductSales

GROUP BY GROUPING SETS (

(City),

(Province),

())

ORDER BY City, Province

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: