SQL道場 データをグループ化する方法
指定した列でデータをグループ化する「GROUP BY句」とグループ化されたものに条件を設定する「HAVING句」についての解説を記載
環境
使用ツール：Microsoft SQL Server Management Studio MySQL Workbench
テーブル設計図
SQLServer
MySQL
GROUP BYの文法
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 列名
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 列名,列名・・・GROUP BY句によりSELECT文で取り出した行を指定された列の値ごとにグループ化することができます。SELECTの後ろの列名にはグループ化した列、集計関数で集計した列のみ指定できます。
テーブルから同じ所属コード（所属）に属している社員数をカウント
| ID | EMP_NAME（社員名） | EMP_NAME_KANA（社員カナ） | EMP_DEPTCD（部署コード） | EMP_AGE（年齢） | EMP_SEX（性別） | ENABLE（有効区分） |
|---|---|---|---|---|---|---|
| 1 | すなりん 太郎 | すなりん たろう | 1 | 35 | 男 | 1 |
| 2 | 博多 ぴーち | はかた ぴーち | 2 | 20 | 女 | 1 |
| 3 | 田中 工事 | たなか こうじ | 2 | 26 | 男 | 1 |
| 4 | 佐々木 花子 | ささき はなこ | 1 | 40 | 女 | 1 |
| 5 | 佐藤 じゅん | さとう じゅん | 1 | 23 | 1 | |
| 6 | 佐々木 ゆん | ささき ゆん | 1 | 23 | 1 |
SQLServer
SELECT
EMP_DEPTCD AS 部署コード,
COUNT(ID) AS 部署内の社員数
FROM [SampleDB].[dbo].[MST_EMPLOYEE]
GROUP BY EMP_DEPTCD
MySQL
SELECT
EMP_DEPTCD AS 部署コード,
COUNT(ID) AS 部署内の社員数
FROM testdb.MST_EMPLOYEE
GROUP BY EMP_DEPTCD
GROUP BYのNG例
SELECTの後ろの列名に「グループ化した列」または「集計関数で集計した列でない列」を指定したためエラーとなっています。SELECTの後に指定する列は、必ずすべてGROUP BY句に含めるか。GROUP BY句に含められない場合は、集計関数を使うようにしましょう。
SELECT
EMP_DEPTCD AS 部署コード,
ID AS ID
FROM [SampleDB].[dbo].[MST_EMPLOYEE]
GROUP BY EMP_DEPTCD「’○○○’」は選択リスト内では無効です。この列は集計関数またはGROUP BY句に含まれません。
ORDER BY句と組み合わせて使用する
GROUP BY句とORDER BY句を組み合わせ使うこともできます。以下の例では、性別でグループピングして、性別ごとの年齢の平均値を求めて降順（年齢が高い順）にソートしています。
| ID | EMP_NAME（社員名） | EMP_NAME_KANA（社員カナ） | EMP_DEPTCD（部署コード） | EMP_AGE（年齢） | EMP_SEX（性別） | ENABLE（有効区分） |
|---|---|---|---|---|---|---|
| 1 | すなりん 太郎 | すなりん たろう | 1 | 35 | 男 | 1 |
| 2 | 博多 ぴーち | はかた ぴーち | 2 | 20 | 女 | 1 |
| 3 | 田中 工事 | たなか こうじ | 2 | 26 | 男 | 1 |
| 4 | 佐々木 花子 | ささき はなこ | 1 | 40 | 女 | 1 |
| 5 | 佐藤 じゅん | さとう じゅん | 1 | 23 | 1 | |
| 6 | 佐々木 ゆん | ささき ゆん | 1 | 23 | 1 |
SELECT
EMP_SEX AS 性別,
AVG(EMP_AGE) AS 平均年齢
FROM [SampleDB].[dbo].[MST_EMPLOYEE]
GROUP BY EMP_SEX
ORDER BY AVG(EMP_AGE) DESC
;
HAVINGの文法
GROUP BY句とHAVING句の組み合わせによりグループ化した中からさらに条件にあったデータのみ選択することができます。
GROUP BY 列名 HAVING 条件式SELECTの後ろの列名にはグループ化した列、集計関数で集計した列のみ指定できます。WHEREの後ろの条件は抽出する列の条件を指定し、HAVINGの後ろの条件はグループ化した列の抽出条件を指定します。
社員マスタからHAVING句で時給の平均値より多いデータを抽出
次の例では、社員マスタの資格列をGROUP BY句でグループピングし、資格ごとの「時給」の平均（AVG）を求めて、HAVING句で700円より大きい資格のデータを抽出しています。
| ID | EMP_NAME（社員名） | EMP_NAME_KANA（社員カナ） | EMP_DEPTCD（部署コード） | EMP_SALARY（時給） | EMP_LICENSE（資格） | ENABLE（有効区分） |
|---|---|---|---|---|---|---|
| 1 | すなりん 太郎 | すなりん たろう | 1 | 900 | 危険物 | 1 |
| 2 | 博多 ぴーち | はかた ぴーち | 2 | 820 | 情報処理 | 1 |
| 3 | 田中 工事 | たなか こうじ | 2 | 1500 | 危険物 | 1 |
| 4 | 佐々木 花子 | ささき はなこ | 1 | 550 | 情報処理 | 1 |
| 5 | 佐藤 じゅん | さとう じゅん | 1 | 700 | なし | 1 |
| 6 | 佐々木 ゆん | ささき ゆん | 1 | 500 | なし | 1 |
SQLServer
SELECT
AVG(EMP_SALARY) AS 時給
,EMP_LICENSE AS 資格
FROM [SampleDB].[dbo].[MST_EMPLOYEE]
GROUP BY EMP_LICENSE HAVING AVG(EMP_SALARY) > 700
;
MySQL
SELECT
AVG(EMP_SALARY) AS 時給,
EMP_LICENSE AS 資格
FROM testdb.MST_EMPLOYEE
GROUP BY EMP_LICENSE HAVING AVG(EMP_SALARY) > 700
;
WHARE句とHAVING句の組み合わせで抽出
社員マスタから資格がなし以外のデータで、「時給」の平均が700より大きい資格のデータのみを選択します。
| ID | EMP_NAME（社員名） | EMP_NAME_KANA（社員カナ） | EMP_DEPTCD（部署コード） | EMP_SALARY（時給） | EMP_LICENSE（資格） | ENABLE（有効区分） |
|---|---|---|---|---|---|---|
| 1 | すなりん 太郎 | すなりん たろう | 1 | 900 | 危険物 | 1 |
| 2 | 博多 ぴーち | はかた ぴーち | 2 | 820 | 情報処理 | 1 |
| 3 | 田中 工事 | たなか こうじ | 2 | 1500 | 危険物 | 1 |
| 4 | 佐々木 花子 | ささき はなこ | 1 | 550 | 情報処理 | 1 |
| 5 | 佐藤 じゅん | さとう じゅん | 1 | 750 | なし | 1 |
| 6 | 佐々木 ゆん | ささき ゆん | 1 | 720 | なし | 1 |
SQLServer
SELECT
AVG(EMP_SALARY) AS 時給
,EMP_LICENSE AS 資格
FROM [SampleDB].[dbo].[MST_EMPLOYEE]
WHERE EMP_LICENSE <> 'なし'
GROUP BY EMP_LICENSE HAVING AVG(EMP_SALARY) > 700
;
MySQL
SELECT
AVG(EMP_SALARY) AS 時給,
EMP_LICENSE AS 資格
FROM testdb.MST_EMPLOYEE
WHERE EMP_LICENSE <> 'なし'
GROUP BY EMP_LICENSE HAVING AVG(EMP_SALARY) > 700
;