SQL GROUP BY句でデータの集計・集約を行う
「社員マスタ」と「売上明細」というテーブルがあります。売上明細テーブルには、社員の売上情報が格納されています。
社員コード | 社員名 | 部署コード | 年齢 |
---|---|---|---|
1 | テスト社員1 | 1 | 22 |
2 | テスト社員2 | 2 | 30 |
3 | テスト社員3 | null | 20 |
売上NO | 社員コード | 売上日 | 売上金額 |
---|---|---|---|
1 | 1 | 20150401 | 200 |
2 | 1 | 20150402 | 300 |
3 | 2 | 20150408 | 100 |
4 | 2 | 20150501 | 150 |
5 | 3 | 20150505 | 550 |
全社員の売上の合計を表示したい。
SQL
は下記のようになります。
SELECT MAX(T1.社員名) AS 社員名
,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
LEFT JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
GROUP BY T1.社員コード
社員名 | 売上金額 |
---|---|
テスト社員1 | 500 |
テスト社員2 | 250 |
テスト社員3 | 550 |
わかりやすくするために、GROUP BY
とSELECT
句の集計関数を行わずに実行した場合の結果を下記に示します。
SELECT T1.社員コード
,T1.社員名
,T2.売上金額
FROM 社員マスタ AS T1
LEFT JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
社員コード | 社員名 | 売上金額 |
---|---|---|
1 | テスト社員1 | 200 |
1 | テスト社員1 | 300 |
2 | テスト社員2 | 100 |
2 | テスト社員2 | 150 |
3 | テスト社員3 | 550 |
売上明細テーブルには1社員につき、複数のデータが存在します。なので、同一社員で複数の明細が出力されてしまいます。
同一社員は1行に集約し、かつ売上金額は集約する行の合計値を表示する必要があります。
下記図のようなイメージです。
社員コード | 社員名 | 売上金額 |
---|---|---|
1 | テスト社員1 | 200+300 |
1 | テスト社員1 | 200 |
1 | テスト社員1 | 300 |
2 | テスト社員2 | 100+150 |
2 | テスト社員2 | 100 |
2 | テスト社員2 | 150 |
3 | テスト社員3 | 550 |
3 | テスト社員3 | 550 |
行を集約するには、GROUP BY
句を使用します。
GROUP BY
句で指定したカラムの値が同じ行は、1行に集約されます。
複数のカラムを指定した場合は、すべてのカラムの値が同じ行をそれぞれ1行に集約します。
上記の例ではGROUP BY
句で社員コードを指定しているので、同一社員(社員コードの値が同じデータ)が1行に集約されます。
GROUP BY
句で指定したカラムは、集約する行の値はすべて同じなので、その値がそのまま出力されます。
GROUP BY
句で指定したカラム以外のカラムはどのように集約するのかを、SELECT
句で指定する必要があります。
集約方法を指定しないと、下記図のように何を出力するのか定まらないからです。
社員コード | 社員名 | 売上金額 |
---|---|---|
1 | テスト社員1?テスト社員1 | 200?300 |
1 | テスト社員1 | 200 |
1 | テスト社員1 | 300 |
2 | テスト社員2?テスト社員2 | 100?150 |
2 | テスト社員2 | 100 |
2 | テスト社員2 | 150 |
3 | テスト社員3 | 550 |
3 | テスト社員3 | 550 |
集計方法の指定は集計関数(集約関数)を使用します。
関数名 | 機能 |
---|---|
COUNT | 集計対象の行数を出力 |
MAX | 集計対象のなかで最大のものを出力 |
MIN | 集計対象のなかで最小のものを出力 |
SUM | 集計対象の合計値を出力 |
AVG | 集計対象の平均を出力 |
例題では、売上金額カラムは売上の合計なのでSUM
関数を指定しています。
社員コードが同じなら、社員名も同じになるはずなので、社員名カラムにはMAX
関数をしています。(最大値でも最小値でも平均値でも値はすべて同じなのでMIN
関数やAVG
関数でも可能)
もしくは上記と同じ理由でGROUP BY
句に社員名も指定するやり方でもできます。
SELECT MAX(T1.社員名) AS 社員名
,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
LEFT JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
GROUP BY T1.社員コード
--または
SELECT T1.社員名
,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
LEFT JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
GROUP BY T1.社員コード, T1.社員名