SQLで集計をしていると、GROUP BYでグループ化して集計した明細レコードと一緒に、小計や合計のレコードを取得したい場合があります。
そこで今回は、1回のSQLでグループ化したデータの明細と一緒に、小計レコードと合計レコードを取得する方法を紹介します。
小計と合計の取得
小計レコードと合計レコードを明細レコードと一緒に取得する際はGROUP BY句で「WITH ROLLUP」修飾子を使用します。
例えば、PCで使用されているWebブラウザーを国ごとに集計するSQLなら以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT ブラウザー, 国, COUNT(*) AS 件数 FROM 使用ブラウザー GROUP BY ブラウザー, 国 ORDER BY ブラウザー, 国 |
GROUP BY句でブラウザーと国でグループ化してCOUNT関数で件数を取得しています。
このSQLのGROUP BY句にWITH ROLLUP修飾子を使ってブラウザーごとの小計と全体の合計レコードを追加します。
|
1 2 3 4 5 6 7 8 9 |
SELECT ブラウザー, 国, COUNT(*) AS 件数 FROM 使用ブラウザー GROUP BY ブラウザー, 国 WITH ROLLUP |
国ごとの小計と全体の合計レコードを追加するなら以下のようになります。
|
1 2 3 4 5 6 7 8 9 |
SELECT ブラウザー, 国, COUNT(*) AS 件数 FROM 使用ブラウザー GROUP BY 国, ブラウザー WITH ROLLUP |
小計、合計取得サンプルSQL
より具体的な例としてデータを集計するためのテーブルを作成し、小計と合計を取得するサンプル用のデータを登録してSQLを作成します。
サンプルのSQLでは学生の成績を科目と性別ごとに集計し、科目ごとの小計レコードと、全データの合計レコードを合わせて取得します。
サンプルSQLで使用するテーブル
テーブルは次の4つのテーブルを作成します。
- 性別マスタテーブル
- 科目マスタテーブル
- 学生マスタテーブル
- 成績テーブル
性別マスタと科目マスタはコードと名称を保持します。
学生マスタは学生の番号と氏名、性別（性別コード）を保持します。
成績テーブルは学生（学生番号）と科目（科目コード）ごとの成績（素点）を保持します。
各テーブルの定義は以下になります。
| 列名 | データ型 | PK |
|---|---|---|
| 性別コード | nvarchar(1) | ○ |
| 性別名称 | nvarchar(1) |
| 列名 | データ型 | PK |
|---|---|---|
| 科目コード | nvarchar(3) | ○ |
| 科目名称 | nvarchar(10) |
| 列名 | データ型 | PK |
|---|---|---|
| 学生番号 | int | ○ |
| 氏名 | nvarchar(20) | |
| 性別コード | nvarchar(1) |
| 列名 | データ型 | PK |
|---|---|---|
| 学生番号 | int | ○ |
| 科目コード | nvarchar(5) | ○ |
| 素点 | int |
テーブルの作成とデータの登録
テーブルのCREATE文とデータを投入するINSERT文です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
-- 性別マスタテーブル CREATE TABLE [性別] ( [性別コード] nvarchar(1) NOT NULL, [性別名称] nvarchar(1) NOT NULL, PRIMARY KEY ([性別コード]) ); -- 科目マスタテーブル CREATE TABLE [科目] ( [科目コード] nvarchar(3) NOT NULL, [科目名称] nvarchar(10) NOT NULL, PRIMARY KEY ([科目コード]) ); -- 学生マスタテーブル CREATE TABLE [学生] ( [学生番号] int NOT NULL, [氏名] nvarchar(20) NOT NULL, [性別コード] nvarchar(1) NOT NULL, PRIMARY KEY ([学生番号]) ); -- 成績テーブル CREATE TABLE [成績] ( [学生番号] int NOT NULL, [科目コード] nvarchar(5) NOT NULL, [素点] int NOT NULL, PRIMARY KEY ([学生番号], [科目コード]) ); -- 性別マスタテーブルデータ INSERT INTO [性別] ([性別コード], [性別名称]) VALUES ('1', '男'), ('2', '女'); -- 科目マスタテーブルデータ INSERT INTO [科目] ([科目コード], [科目名称]) VALUES ('100', '国語'), ('200', '数学'), ('300', '英語'); -- 学生マスタテーブルデータ INSERT INTO [学生] ([学生番号], [氏名], [性別コード]) VALUES (1, '赤城 太郎', '1'), (2, '青山 花子', '2'), (3, '井上 由衣', '2'), (4, '上田 圭太', '1'), (5, '岡本 正一', '1'); -- 成績テーブルデータ INSERT INTO [成績] ([学生番号], [科目コード], [素点]) VALUES (1, '100', 85), (1, '200', 75), (1, '300', 80), (2, '100', 63), (2, '200', 94), (2, '300', 72), (3, '100', 71), (3, '200', 69), (3, '300', 59), (4, '100', 68), (4, '200', 81), (4, '300', 90), (5, '100', 92), (5, '200', 96), (5, '300', 94); |
まずは成績情報を科目と性別でグループ化します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 科目、性別ごとの点数集計 SELECT [成績].[科目コード], [学生].[性別コード], SUM([成績].[素点]) AS [点数] FROM [成績] -- 科目と性別ごとの集計データを取得するために -- 成績テーブルと学生マスタテーブルを結合する LEFT OUTER JOIN [学生] ON [成績].[学生番号] = [学生].[学生番号] GROUP BY [成績].[科目コード], [学生].[性別コード] |
これで科目と性別ごとの点数の集計（SUM）を取得できました。
次に科目ごとの小計レコードと合計レコードを取得するので、GROUP BY句の性別コードの後にWITH ROLLUP修飾子を付けます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 科目、性別ごとの点数集計 + 科目ごとの小計 + 全体の合計 SELECT [成績].[科目コード], [学生].[性別コード], SUM([成績].[素点]) AS [点数] FROM [成績] LEFT OUTER JOIN [学生] ON [成績].[学生番号] = [学生].[学生番号] GROUP BY [成績].[科目コード], -- WITH ROLLUP修飾子を付ける [学生].[性別コード] WITH ROLLUP |
これで科目と性別ごとの集計レコードと科目ごとに集計した小計レコード（男女の合計レコード）と全体の合計レコードが取得できました。
このままだと科目コード、性別コード、集計した点数（素点）の3列しかないので、分かりやすくするために科目名称と性別名称を追加します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- 集計データにマスタテーブルを結合して名称を取得 SELECT [学生成績].[科目コード], [科目].[科目名称], [学生成績].[性別コード], [性別].[性別名称], [学生成績].[点数] FROM ( SELECT [成績].[科目コード], [学生].[性別コード], SUM([成績].[素点]) AS [点数] FROM [成績] LEFT OUTER JOIN [学生] ON [成績].[学生番号] = [学生].[学生番号] GROUP BY [成績].[科目コード], -- WITH ROLLUP修飾子を付ける [学生].[性別コード] WITH ROLLUP ) AS [学生成績] LEFT OUTER JOIN [科目] ON [学生成績].[科目コード] = [科目].[科目コード] LEFT OUTER JOIN [性別] ON [学生成績].[性別コード] = [性別].[性別コード] |
最後に小計レコードと合計レコードの科目と性別がNULLになっているので変換します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
-- 小計レコードと合計レコードの文字列を変換 SELECT [学生成績].[科目コード], CASE WHEN [学生成績].[科目コード] IS NULL THEN '総合計' ELSE [科目].[科目名称] END AS [科目名称], [学生成績].[性別コード], CASE WHEN [学生成績].[科目コード] IS NULL THEN '合計 (科目性別計)' WHEN [学生成績].[性別コード] IS NULL THEN '小計 (男女計)' ELSE [性別].[性別名称] END AS [性別名称], [学生成績].[点数] FROM ( SELECT [成績].[科目コード], [学生].[性別コード], SUM([成績].[素点]) AS [点数] FROM [成績] LEFT OUTER JOIN [学生] ON [成績].[学生番号] = [学生].[学生番号] GROUP BY [成績].[科目コード], -- WITH ROLLUP修飾子を付ける [学生].[性別コード] WITH ROLLUP ) AS [学生成績] LEFT OUTER JOIN [科目] ON [学生成績].[科目コード] = [科目].[科目コード] LEFT OUTER JOIN [性別] ON [学生成績].[性別コード] = [性別].[性別コード] |
小計レコードと合計レコードをCASE式でIS NULLを使用して判断していますが、GROUPING関数が用意されているデータベースであれば、置き換えることで同じ結果を得ることもできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
-- GROUPING関数を使用して小計レコードと合計レコードを判断 SELECT [学生成績].[科目コード], CASE WHEN [学生成績].IS_GROUPING_SUBJECT = 1 THEN '総合計' ELSE [科目].[科目名称] END AS [科目名称], [学生成績].[性別コード], CASE WHEN [学生成績].IS_GROUPING_SUBJECT = 1 THEN '合計 (科目性別計)' WHEN [学生成績].IS_GROUPING_GENDER = 1 THEN '小計 (男女計)' ELSE [性別].[性別名称] END AS [性別名称], [学生成績].[点数] FROM ( SELECT [成績].[科目コード], [学生].[性別コード], GROUPING([成績].[科目コード]) AS IS_GROUPING_SUBJECT, GROUPING([学生].[性別コード]) AS IS_GROUPING_GENDER, SUM([成績].[素点]) AS [点数] FROM [成績] LEFT OUTER JOIN [学生] ON [成績].[学生番号] = [学生].[学生番号] GROUP BY [成績].[科目コード], -- WITH ROLLUP修飾子を付ける [学生].[性別コード] WITH ROLLUP ) AS [学生成績] LEFT OUTER JOIN [科目] ON [学生成績].[科目コード] = [科目].[科目コード] LEFT OUTER JOIN [性別] ON [学生成績].[性別コード] = [性別].[性別コード] |
関連情報補足
本記事のINSERT INTOステートメントは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに挿入する方法については、以下の記事を参照してください。
