DOT NET RULES

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