GROUP BYでグループ化する

グループごとに集計するGROUP BY

行をいくつかのグループに分けてから集計するには、GROUP BY句を使います。

SELECT グループ化する列, 集計関数(列名)
FROM テーブル名
GROUP BY グループ化する列;

crewテーブルで、役割（role）ごとの人数と平均給料を求めてみましょう。トウマとリョウは、前回学んだとおり配属先が決まっておらずhomeがNULLです。

crew テーブル：

id	name	role	age	home	salary
1	ユミ	エンジニア	24	地球	320000
2	ケンジ	パイロット	35	火星	400000
3	サクラ	医師	41	地球	520000
4	ハルト	医師	33	月	450000
5	レイ	研究員	29	火星	380000
6	ミドリ	パイロット	27	地球	360000
7	ソラ	エンジニア	45	月	470000
8	トウマ	研究員	24	NULL	270000
9	アカリ	エンジニア	31	火星	350000
10	リョウ	パイロット	38	NULL	440000

SELECT role, COUNT(*) AS 人数, AVG(salary) AS 平均給料
FROM crew
GROUP BY role;

結果：

role	人数	平均給料
エンジニア	3	380000
パイロット	3	400000
医師	2	485000
研究員	2	325000

GROUP BY roleと書くことで、SQLはまずroleの値が同じ行同士を1つのグループにまとめます。そのうえで、グループごとにCOUNT(*)やAVG(salary)を計算しているのです。

今回のcrewテーブルはsalaryにNULLの乗員がいないため、それぞれのグループに含まれる全員分の値で平均が計算されています。前回学んだ「集計関数はNULLを自動的に除外する」というルールは、GROUP BYでグループごとに計算するときも変わらず働きます。もしsalaryがNULLの乗員がいれば、その行はやはりグループの集計から自動的に除かれます。

SELECTに書ける列のルール

GROUP BYを使うとき、SELECTに書ける列には決まりがあります。書けるのは、次の2種類だけです。

1. GROUP BYに指定した列
2. 集計関数（COUNT・SUM・AVGなど）

たとえば、次のようなSQLはエラーになるか、意図しない結果になります。

-- × 危険：nameはGROUP BYにも集計関数にも入っていない
SELECT name, role, AVG(salary)
FROM crew
GROUP BY role;

roleでグループ化すると、「エンジニア」グループにはユミ・ソラ・アカリの3人が含まれます。ここでnameをそのまま選ぼうとしても、SQLは「3人のうち、どの名前を表示すればいいのか」を決められません。1つのグループに複数の行がある以上、集計されていない列をそのまま出すことはできないのです。

正しくは、こう書きます。

-- ○ 正しい：GROUP BYの列と集計関数だけ
SELECT role, AVG(salary) AS 平均給料
FROM crew
GROUP BY role;

「GROUP BYした列」か「集計した値」しかSELECTに書けない——これはGROUP BYを使ううえで、最初につまずきやすいルールです。ここでしっかり押さえておきましょう。

GROUP BYを使えば、「役割ごと」「出身地ごと」など、好きな切り口でデータをまとめて眺められるようになります。次回は、そのグループ自体をさらに条件で絞り込むHAVINGを学びます。

/sql/game/ の SQL DIVER で、GROUP BYによるグループ集計を実際にタイプして確かめてみましょう。