-- GROUPING SETS
-- Grouping Sets in SQL Server 2008<?xml:namespace prefix = o />
-- link : http://www.sql-server-performance.com/articles/dba/Grouping_Sets_in_SQL_Server_2008_p1.aspx
Use master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'GroupingSetsDemo')
DROP DATABASE [GroupingSetsDemo]
GO
CREATE DATABASE GroupingSetsDemo
GO
USE GroupingSetsDemo
GO
/* Drop CricketTeams Table if already exists */
IF OBJECT_ID (N'dbo.CricketTeams', N'U') IS NOT NULL
DROP TABLE dbo.CricketTeams;
GO
/* Create CricketTeams Table */
CREATE TABLE dbo.CricketTeams
(
CricketTeamID TINYINT NOT NULL PRIMARY KEY,
CricketTeamShortCode NVARCHAR(30),
CricketTeamCountry NVARCHAR(30),
CricketTeamContinent NVARCHAR(50)
)
GO
/* Populate the CricketTeams Table */
INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (1, 'AUS', 'Australia', 'Australia')
GO
INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (2, 'IND', 'India', 'Asia')
GO
INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (3, 'PAK', 'Pakistan', 'Asia')
GO
INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (4, 'SRL', 'Srilanka', 'Asia')
GO
INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (5, 'SAF', 'South Africa', 'Africa')
GO
INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (6, 'ZIM', 'Zimbabwe', 'Africa')
GO
USE GroupingSetsDemo
GO
SELECT CricketTeamShortCode, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamShortCode
GO
SELECT CricketTeamCountry, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamCountry
GO
SELECT CricketTeamContinent, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamContinent
GO
-- SQL2005
Use GroupingSetsDemo
GO
SELECT CricketTeamContinent, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamContinent
UNION ALL
SELECT CricketTeamCountry, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamCountry
UNION ALL
SELECT CricketTeamShortCode, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamShortCode
GO
--Result
CricketTeamContinent (No column name)
Africa 2
Asia 3
Australia 1
Australia 1
India 1
Pakistan 1
South Africa 1
Srilanka 1
Zimbabwe 1
AUS 1
IND 1
PAK 1
SAF 1
SRL 1
ZIM 1
-- Using Grouping Sets Clause Introduced in SQL Server 2008
Use GroupingSetsDemo
GO
/* Using the Grouping Sets clause introduced in SQL Server 2008 */
-- 집계를 위한 그룹핑 지원이 가능하다뉘..ㅎㅎㅎㅎ
SELECT CricketTeamShortCode, CricketTeamCountry, CricketTeamContinent, Count(*) AS Count
FROM CricketTeams
GROUP BY GROUPING SETS ( -- grouping set
(CricketTeamShortCode),
(CricketTeamCountry),
(CricketTeamContinent),
()
)
ORDER BY CricketTeamShortCode, CricketTeamCountry, CricketTeamContinent
GO
-- result
CricketTeamShortCode CricketTeamCountry CricketTeamContinent Count
NULL NULL NULL 6
NULL NULL Africa 2
NULL NULL Asia 3
NULL NULL Australia 1
NULL Australia NULL 1
NULL India NULL 1
NULL Pakistan NULL 1
NULL South Africa NULL 1
NULL Srilanka NULL 1
NULL Zimbabwe NULL 1
AUS NULL NULL 1
IND NULL NULL 1
PAK NULL NULL 1
SAF NULL NULL 1
SRL NULL NULL 1
ZIM NULL NULL 1
점점많이 좋아지는 군요...^^
가끔 집계를 위해서 쓸때 좋을듯.ㅎㅎ