Grouping Sets in SQL Server 2008

by Amol 15. February 2011 19:31

 

IF OBJECT_ID ('[dbo].[Wrestler]') IS NOT NULL
DROP TABLE [dbo].[Wrestler]
GO 

CREATE TABLE [dbo].[Wrestler]
(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(255) NOT NULL,
Country VARCHAR(50) NOT NULL,
[State] VARCHAR(50)
)

GO

INSERT INTO [dbo].[Wrestler]
SELECT 'John Cena', 'USA', 'West Newbury' UNION ALL
SELECT 'Batista', 'USA', 'Washington DC' UNION ALL
SELECT 'Ray Mysterio', 'USA', 'California' UNION ALL
SELECT 'Garcia Twins', 'USA', 'California' UNION ALL
SELECT 'Great Khali', 'INDIA', 'Punjab' UNION ALL
SELECT 'Undertaker', 'USA', 'TEXAS' 

GO

SELECT Name, COUNT(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY Name 

UNION ALL

SELECT Country, COUNT(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY Country 

UNION ALL

SELECT [State], COUNT(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY [State] 

GO

SELECT Name, [State], Country, Count(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY GROUPING SETS
(
Name,
[State],
Country,
()
)
ORDER BY Name, [State], Country


Tags:

SQL Server Scripts

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Category

Recent Posts

Tag cloud