GROUP BY で小計や総計を取得する

SQL集計関数 (Transact-SQL) を使う場合、よくセットで紹介される GROUP BY (Transact-SQL) ですがグループ毎の計や総計を取得する ROLLUP、CUBE、GROUPING SETS が用意されています。
ということで、試してみました。

テーブル/データの作成

CREATE TABLE [売上テーブル] (
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [売上日] [date] NOT NULL,
  [部門] [nvarchar](50) NOT NULL,
  [社員] [nvarchar](50) NOT NULL,
  [売上金額] [decimal](18, 0) NOT NULL
)
GO

INSERT INTO [売上テーブル] ( [売上日] ,[部門] ,[社員] ,[売上金額])
VALUES ( '2011/4/1' ,N'A部門' ,N'田中' ,1000), 
( '2011/4/2' ,N'A部門' ,N'伊藤' ,2000), 
( '2011/4/3' ,N'A部門' ,N'田中' ,3000), 
( '2011/4/4' ,N'B部門' ,N'佐藤' ,4000), 
( '2011/4/5' ,N'A部門' ,N'伊藤' ,5000), 
( '2011/4/6' ,N'A部門' ,N'田中' ,6000), 
( '2011/4/7' ,N'B部門' ,N'佐藤' ,7000), 
( '2011/4/8' ,N'B部門' ,N'佐藤' ,8000), 
( '2011/4/9' ,N'B部門' ,N'田中' ,9000)
GO

データの確認

SELECT * FROM [売上テーブル]


このテーブルから、部門、社員の計(売上金額)と部門の計と全体の計 を取得したい!という場合

ROLLUP を使う

SELECT [部門], [社員], SUM([売上金額]) AS [売上金額計]
FROM [売上テーブル]
GROUP BY ROLLUP( [部門], [社員] )


部門、社員の計(売上金額)と部門の計 だけで良い場合

GROUPING SETS を使う

SELECT [部門], [社員], SUM([売上金額]) AS [売上金額計]
FROM [売上テーブル]
GROUP BY GROUPING SETS ( ( [部門], [社員] ), ( [部門] ) )


ちなみに GROUPING SETS でも ROLLUP と同じ結果を返す事が出来ます。

SELECT [部門], [社員], SUM([売上金額]) AS [売上金額計]
FROM [売上テーブル]
GROUP BY GROUPING SETS ( ( [部門], [社員] ), ( [部門] ), () )

これで、ROLLUP と同じ結果になります。


小計や総計を取得したいケースは帳票で多いと思います。帳票ツールは結構賢くて設定のみで集計してくれたりするので、使う機会が少ないかもしれませんが知ってると役に立つ時があるかもしれませんね。

クエリサンプル

CREATE TABLE [売上テーブル] (
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [売上日] [date] NOT NULL,
  [部門] [nvarchar](50) NOT NULL,
  [社員] [nvarchar](50) NOT NULL,
  [売上金額] [decimal](18, 0) NOT NULL
)
GO

INSERT INTO [売上テーブル] ( [売上日] ,[部門] ,[社員] ,[売上金額])
VALUES ( '2011/4/1' ,N'A部門' ,N'田中' ,1000), 
( '2011/4/2' ,N'A部門' ,N'伊藤' ,2000), 
( '2011/4/3' ,N'A部門' ,N'田中' ,3000), 
( '2011/4/4' ,N'B部門' ,N'佐藤' ,4000), 
( '2011/4/5' ,N'A部門' ,N'伊藤' ,5000), 
( '2011/4/6' ,N'A部門' ,N'田中' ,6000), 
( '2011/4/7' ,N'B部門' ,N'佐藤' ,7000), 
( '2011/4/8' ,N'B部門' ,N'佐藤' ,8000), 
( '2011/4/9' ,N'B部門' ,N'田中' ,9000)
GO

SELECT * FROM [売上テーブル]

SELECT [部門], [社員], SUM([売上金額]) AS [売上金額計]
FROM [売上テーブル]
GROUP BY ROLLUP( [部門], [社員] )

SELECT [部門], [社員], SUM([売上金額]) AS [売上金額計]
FROM [売上テーブル]
GROUP BY GROUPING SETS ( ( [部門], [社員] ), ( [部門] ) )

DROP TABLE [売上テーブル]
GO