マスターテーブルのレコードごとの集計値を取得するSQL

テーブルに登録されている金額などの数値データを、あるマスターテーブルのキー（コードやID）ごとに集計します。

1 サンプルテーブルの作成
- 1.1 テーブル定義
- 1.2 CREATE SQL
2 テストデータ
- 2.1 データ
- 2.2 INSERT SQL
3 データを地域ごとに集計する
4 地域マスタテーブルのデータごとに集計する

サンプルテーブルの作成

マスターテーブルのレコードごとの集計値を取得するサンプルSQLを作成するためのテーブルを作成します。

テーブル定義

テーブルは「地域マスタ」テーブルと「販売」テーブルを作成します。

地域マスタテーブル

列名	データ型(桁数）
地域コード	nvarchar(2)
地域名	nvarchar(5)

販売テーブル

列名	データ型(桁数）
販売ID	int
地域コード	nvarchar(2)
金額	decimal(9, 0)

CREATE SQL

テーブルのCREATE文は以下のようになります。

地域マスタテーブル

CREATE TABLE [地域マスタ]
(
  [地域コード] nvarchar(2) NOT NULL,
  [地域名]     nvarchar(5) NOT NULL
);

CREATE TABLE [地域マスタ]

(

[地域コード] nvarchar(2) NOT NULL,

[地域名] nvarchar(5) NOT NULL

);

販売テーブル

CREATE TABLE [販売]
(
  [販売ID]     int         NOT NULL PRIMARY KEY,
  [地域コード] nvarchar(2) NOT NULL,
  [金額]       decimal(9, 0) NOT NULL
);

CREATE TABLE [販売]

(

[販売ID] int NOT NULL PRIMARY KEY,

[地域コード] nvarchar(2) NOT NULL,

[金額] decimal(9, 0) NOT NULL

);

テストデータ

作成したテーブルには、以下のデータを登録します。

データ

地域マスタテーブル

地域コード	地域名
08	茨城県
09	栃木県
10	群馬県
11	埼玉県
12	千葉県
13	東京都
14	神奈川県

販売テーブル

販売ID	地域コード	金額
1	09	153000
2	12	535000
3	09	916000
4	11	186000
5	12	258000
6	13	854000
7	14	455000
8	09	362000
9	11	622000
10	12	487000
11	09	1018000
12	13	265000
13	11	334000
14	11	568000
15	14	154000
16	12	98000
17	11	254000
18	14	366000
19	13	452000
20	14	723000
21	13	654000
22	13	819000

INSERT SQL

テーブルのINSERT文は以下のようになります。

地域マスタテーブル

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('08', '茨城県');
INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('09', '栃木県');
INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('10', '群馬県');
INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('11', '埼玉県');
INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('12', '千葉県');
INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('13', '東京都');
INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('14', '神奈川県');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('08', '茨城県');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('09', '栃木県');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('10', '群馬県');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('11', '埼玉県');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('12', '千葉県');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('13', '東京都');

INSERT INTO [地域マスタ] ([地域コード], [地域名]) VALUES ('14', '神奈川県');

販売テーブル

INSERT INTO [販売]
([販売ID], [地域コード], [金額])
VALUES
(1, '09', 153000),
(2, '12', 535000),
(3, '09', 916000),
(4, '11', 186000),
(5, '12', 258000),
(6, '13', 854000),
(7, '14', 455000),
(8, '09', 362000),
(9, '11', 622000),
(10, '12', 487000),
(11, '09', 1018000),
(12, '13', 265000),
(13, '11', 334000),
(14, '11', 568000),
(15, '14', 154000),
(16, '12', 98000),
(17, '11', 254000),
(18, '14', 366000),
(19, '13', 452000),
(20, '14', 723000),
(21, '13', 654000),
(22, '13', 819000);

INSERT INTO [販売]

([販売ID], [地域コード], [金額])

VALUES

(1, '09', 153000),

(2, '12', 535000),

(3, '09', 916000),

(4, '11', 186000),

(5, '12', 258000),

(6, '13', 854000),

(7, '14', 455000),

(8, '09', 362000),

(9, '11', 622000),

(10, '12', 487000),

(11, '09', 1018000),

(12, '13', 265000),

(13, '11', 334000),

(14, '11', 568000),

(15, '14', 154000),

(16, '12', 98000),

(17, '11', 254000),

(18, '14', 366000),

(19, '13', 452000),

(20, '14', 723000),

(21, '13', 654000),

(22, '13', 819000);

上記の販売テーブルへのデータのインサートは、1回の実行で複数のレコードを挿入する形式のSQLで記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。

データを地域ごとに集計する

地域コードごとに販売テーブルのデータを集計します。
SQLは次のようになります。

SELECT
	[地域コード],
	SUM([金額]) AS [合計金額]
FROM
	[販売]
GROUP BY
	[地域コード]
ORDER BY
	[地域コード];

SELECT

[地域コード],

SUM([金額]) AS [合計金額]

FROM

[販売]

GROUP BY

[地域コード]

ORDER BY

[地域コード];

GROUP BY句で地域コードを指定して、金額列をSUM関数で集計しています。

実行結果

地域マスタテーブルのデータごとに集計する

上記のSQLでは販売テーブルに登録されていない「08: 茨城県」と「10: 群馬県」のデータが集計されません。
そこでLEFT OUTER JOINを使用して、地域マスタテーブルと販売テーブルを左外部結合してから集計します。
SQLは次のようになります。

SELECT
	[地域マスタ].[地域コード],
	SUM([販売].[金額]) AS [合計金額]
FROM
	[地域マスタ]
LEFT OUTER JOIN
	[販売]
ON
	[地域マスタ].[地域コード] = [販売].[地域コード]
GROUP BY
	[地域マスタ].[地域コード]
ORDER BY
	[地域マスタ].[地域コード];

SELECT

[地域マスタ].[地域コード],

SUM([販売].[金額]) AS [合計金額]

FROM

[地域マスタ]

LEFT OUTER JOIN

[販売]

[地域マスタ].[地域コード] = [販売].[地域コード]

GROUP BY

[地域マスタ].[地域コード]

ORDER BY

[地域マスタ].[地域コード];

実行結果

このままでは販売テーブルに登録されていない地域コードの合計金額がNULLになってしまいます。
そこでISNULL関数を使用して、販売テーブルの金額列の集計値がNULLの場合は0に変換します。
SQLは次のようになります。

SELECT
	[地域マスタ].[地域コード],
	ISNULL(SUM([販売].[金額]), 0) AS [合計金額]
FROM
	[地域マスタ]
LEFT OUTER JOIN
	[販売]
ON
	[地域マスタ].[地域コード] = [販売].[地域コード]
GROUP BY
	[地域マスタ].[地域コード]
ORDER BY
	[地域マスタ].[地域コード];