SQLのSELECT文でレコードをグループ化して検索（抽出）する GROUP BY

SQLではレコード（データ）を取得する際に、SELECTステートメントを使用します。

SELECTステートメントで取得するレコードは、テーブルに登録されているものになりますが、レコードを何らかの区分ごとにまとめた結果の値を取得したい場合があります。

例えば、従業員や学生などの人のデータを性別ごとにまとめて人数を取得する場合や、商品データをカテゴリーごとにまとめて売上金額を取得する場合などです。

そこで今回は、SELECTステートメントを使用してデータを取得する際に、特定の区分（列の値）をもとにグループ化する方法について紹介します。

1 データの取得に使用するサンプルテーブル
2 レコードをグループ化する「GROUP BY」

データの取得に使用するサンプルテーブル

本記事では、データを取得するためのサンプルテーブルとして、以下のテーブルを使用します。

テーブル定義

サンプルテーブルは、学生情報を管理するための「学生」テーブルと、成績情報を管理するための「成績」テーブルを作成します。

学生テーブルには、学生情報を一意に判別するための主キー（プライマリーキー）となる「学籍番号」、氏名を管理するための「氏名」、学生の性別を管理する「性別（男性、女性）」の3つの列を作成します。

列名	データ型	主キー
学籍番号	int	○
氏名	nvarchar(10)
性別	nvarchar(2)

成績テーブルには、「学籍番号」、科目を管理する「科目（数学、英語）」、科目の得点を管理する「得点」の3つの列を作成します。

列名	データ型	主キー
学籍番号	int	○
科目	nvarchar(2)	○
得点	int

テーブルを作成するCREATE文のSQL

学生テーブルを作成するSQLは次のようになります。

CREATE TABLE [学生]
(
    [学籍番号] int NOT NULL PRIMARY KEY,
    [氏名] nvarchar(10) NOT NULL,
    [性別] nvarchar(2) NOT NULL
);

CREATE TABLE [学生]

(

[学籍番号] int NOT NULL PRIMARY KEY,

[氏名] nvarchar(10) NOT NULL,

[性別] nvarchar(2) NOT NULL

);

成績テーブル作成するSQLは次のようになります。

CREATE TABLE [成績]
(
    [学籍番号] int NOT NULL,
    [科目] nvarchar(2) NOT NULL,
    [得点] int NOT NULL,
    PRIMARY KEY([学籍番号], [科目])
);

CREATE TABLE [成績]

(

[学籍番号] int NOT NULL,

[科目] nvarchar(2) NOT NULL,

[得点] int NOT NULL,

PRIMARY KEY([学籍番号], [科目])

);

テーブルにレコードを挿入するINSERT文のSQL

学生テーブルにはサンプルデータとして以下のレコードを挿入します。

INSERT INTO [学生]
    ([学籍番号], [氏名], [性別])
VALUES
    (1001, '山田　太郎', '男性'),
    (1002, '佐藤　一郎', '男性'),
    (1003, '鈴木　花子', '女性'),
    (1004, '中村　幸一', '男性'),
    (1005, '二階堂　桜', '女性');

INSERT INTO [学生]

([学籍番号], [氏名], [性別])

VALUES

(1001, '山田　太郎', '男性'),

(1002, '佐藤　一郎', '男性'),

(1003, '鈴木　花子', '女性'),

(1004, '中村　幸一', '男性'),

(1005, '二階堂　桜', '女性');

成績テーブルにはサンプルデータとして以下のレコードを挿入します。

INSERT INTO [成績]
    ([学籍番号], [科目], [得点])
VALUES
    (1001, '数学', 85),
    (1001, '英語', 72),
    (1002, '数学', 68),
    (1002, '英語', 91),
    (1003, '数学', 77),
    (1003, '英語', 63),
    (1004, '数学', 59),
    (1004, '英語', 62),
    (1005, '数学', 95),
    (1005, '英語', 83);

INSERT INTO [成績]

([学籍番号], [科目], [得点])

VALUES

(1001, '数学', 85),

(1001, '英語', 72),

(1002, '数学', 68),

(1002, '英語', 91),

(1003, '数学', 77),

(1003, '英語', 63),

(1004, '数学', 59),

(1004, '英語', 62),

(1005, '数学', 95),

(1005, '英語', 83);

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

レコードをグループ化する「GROUP BY」

SELECTステートメントで取得するレコードをグループ化する際は、「GROUP BY」というキーワードを使用します。

GROUP BYは、SELECTステートメントのFROM句の後に記述します。

SELECT
    [取得する列1], [取得する列2], [取得する列3] ...
FROM
    [取得先のテーブル]
GROUP BY
    [グループ化のキー列1], [グループ化のキー列2] ...;

SELECT

[取得する列1], [取得する列2], [取得する列3] ...

FROM

[取得先のテーブル]

GROUP BY

[グループ化のキー列1], [グループ化のキー列2] ...;

GROUP BYの後には、グループ化する際のキーとなる列を指定します。

GROUP BYを使用して取得されるレコードは、GROUP BY句で指定したキー列の値が同じものが1件にまとめられます。

例えばチームという列に、Aという値を持つレコードが3つ、Bという値を持つレコードが2つ、Cという値を持つレコードが4つが登録されているテーブルからデータを取得する際にGROUP BYのキー列にチームを指定すると、A、B、Cの3レコードにまとめた結果が返されます。

以下では、作成したテーブルを使用して、GROUP BYを使用してデータを取得する例をいくつか紹介します。

学生の男女別の人数を取得する

学生テーブルのデータを男女別に取得して人数を算出するSQLは以下のようになります。

SELECT
    [性別],
    COUNT(*) AS [人数]
FROM
    [学生]
GROUP BY
    [性別];

SELECT

[性別],

COUNT(*) AS [人数]

FROM

[学生]

GROUP BY

[性別];

データの取得結果

性別	人数
女性	2
男性	3

GROUP BYのキー列に性別を指定することで、女性と男性のデータが取得されます。
GROUP BYでグループ化したレコードは集計関数で件数や合計などを取得することができます。
上記の例ではCOUNT関数を使用して、男女別の人数を取得しています。

参考資料 SQL Serverの集計関数

成績の科目別の最高点、最低点、平均点を取得する

成績テーブルのデータをから、科目別の最高点、最低点、平均点を取得するSQLは以下のようになります。

SELECT
    [科目],
    MAX([得点]) AS [最高点],
    MIN([得点]) AS [最低点],
    AVG([得点]) AS [平均点]
FROM
    [成績]
GROUP BY
    [科目];

SELECT

[科目],

MAX([得点]) AS [最高点],

MIN([得点]) AS [最低点],

AVG([得点]) AS [平均点]

FROM

[成績]

GROUP BY

[科目];

データの取得結果

科目	最高点	最低点	平均点
数学	95	59	76
英語	91	62	74

GROUP BYのキー列に科目を指定し、科目ごとの得点を分析するために、MAX関数で最高得点を、MIN関数で最低得点を、AVG関数で平均点を取得しています。

成績から男女別＋科目別の最高点、最低点、平均点を取得する

最後に学生テーブルと成績テーブルを結合して、性別ごと（男女別）に各科目の最高点、最低点、平均点を取得する例を示します。

SELECT
    [性別],
    [科目],
    MAX([得点]) AS [最高点],
    MIN([得点]) AS [最低点],
    AVG([得点]) AS [平均点]
FROM
	[学生]
INNER JOIN
    [成績]
ON
    [学生].[学籍番号] = [成績].[学籍番号]
GROUP BY
    [性別],
    [科目];

SELECT

[性別],

[科目],

MAX([得点]) AS [最高点],

MIN([得点]) AS [最低点],

AVG([得点]) AS [平均点]

FROM

[学生]

INNER JOIN

[成績]

[学生].[学籍番号] = [成績].[学籍番号]

GROUP BY

[性別],

[科目];

データの取得結果

性別	科目	最高点	最低点	平均点
女性	数学	95	77	86
男性	数学	85	59	70
女性	英語	83	63	73
男性	英語	91	62	75

学生テーブルと成績テーブルをINNER JOINで内部結合し、GROUP BYのキー列に性別と科目を指定して、男女別＋科目別の最高点、最低点、平均点を取得しています。

補足

ここまでで、成績から科目別の得点を取得する例と、性別＋科目別の得点を取得する例を別々に示しましたが、この2つのSQLをもとに1つの結果表として、男女別のそれぞれの得点と男女の合計を取得することも可能です。
2つのSQLを1つのSQLとして実行するためには、UNION句を使用します。

UNION句を使用して成績の得点データを取得するSQLは、以下のようになります。

SELECT
    [性別],
    [科目],
    MAX([得点]) AS [最高点],
    MIN([得点]) AS [最低点],
    AVG([得点]) AS [平均点]
FROM
    [学生]
INNER JOIN
    [成績]
ON
    [学生].[学籍番号] = [成績].[学籍番号]
GROUP BY
    [性別],
    [科目]
UNION
SELECT
    '合計' AS [性別],
    [科目],
    MAX([得点]) AS [最高点],
    MIN([得点]) AS [最低点],
    AVG([得点]) AS [平均点]
FROM
    [成績]
GROUP BY
    [科目]
ORDER BY
    [科目],
    [性別];

SELECT

[性別],

[科目],

MAX([得点]) AS [最高点],

MIN([得点]) AS [最低点],

AVG([得点]) AS [平均点]

FROM

[学生]

INNER JOIN

[成績]

[学生].[学籍番号] = [成績].[学籍番号]

GROUP BY

[性別],

[科目]

UNION

SELECT

'合計' AS [性別],

[科目],

MAX([得点]) AS [最高点],

MIN([得点]) AS [最低点],

AVG([得点]) AS [平均点]

FROM

[成績]

GROUP BY

[科目]

ORDER BY

[科目],

[性別];

データの取得結果

性別	科目	最高点	最低点	平均点
合計	数学	95	59	76
女性	数学	95	77	86
男性	数学	85	59	70
合計	英語	91	62	74
女性	英語	83	63	73
男性	英語	91	62	75