GROUPING SETS

-- 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

 

점점많이 좋아지는 군요...^^

가끔 집계를 위해서 쓸때 좋을듯.ㅎㅎ