SQL GROUP BY句

概要: このチュートリアルでは、SQLのGROUP BY句を使用して、1つ以上の列に基づいて行をグループ化する方法を学習します。

SQL GROUP BY句の概要

GROUP BY句は、SELECT文のオプション句です。 GROUP BY句を使用すると、1つ以上の列の値に基づいて行をグループ化できます。グループごとに1行が返されます。

GROUP BY句の基本構文を以下に示します。

SELECT
	column1,
	column2,
	aggregate_function(column3)
FROM
	table_name
GROUP BY
	column1,
	column2;Code language: SQL (Structured Query Language) (sql)

次の図は、GROUP BY句の動作を示しています。

左側のテーブルには、idとfruitの2つの列があります。 fruit列にGROUP BY句を適用すると、fruit列の一意の値を含む結果セットが返されます。

SELECT 
    fruit
FROM 
    sample_table
GROUP BY 
    fruit;

実際には、GROUP BY句は、MIN、MAX、AVG、SUM、COUNTなどの集約関数と組み合わせて使用して、各グループの情報を提供する指標を計算することがよくあります。

たとえば、GROUP BY句がCOUNT集約関数とどのように連携するかを以下に示します。

この例では、fruit列の値でグループ化し、COUNT関数をid列に適用します。結果セットには、fruit列の一意の値と対応する行の数が含まれます。

SELECT 
   fruit, COUNT(id) 
FROM
   sample_table
GROUP BY 
   fruit;

GROUP BY句に表示される列は、*グループ化列*と呼ばれます。グループ化列にNULL値が含まれている場合、GROUP BY句はすべてのNULL値を等しいと見なすため、すべてのNULL値が1つのグループにまとめられます。

SQL GROUP BYの例

GROUP BY句の動作を示すために、サンプルデータベースのemployeesテーブルとdepartmentsテーブルを使用します。

次の例では、GROUP BY句を使用して、employeesテーブルのdepartment_id列の値をグループ化します。

SELECT 
    department_id
FROM 
    employees
GROUP BY 
    department_id;Code language: SQL (Structured Query Language) (sql)

出力

試してみる

+---------------+
| department_id |
+---------------+
|             1 |
|             2 |
|             3 |
|             4 |
|             5 |
|             6 |
|             7 |
|             8 |
|             9 |
|            10 |
|            11 |
+---------------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

この例では

まず、SELECT句は、employeesテーブルのdepartment_id列からすべての値を返します。
次に、GROUP BY句はすべての値をグループにグループ化します。

employeesテーブルのdepartment_id列には、重複するdepartment_id値を含む40行があります。ただし、GROUP BYはこれらの値をグループにグループ化します。

集約関数がない場合、GROUP BYはDISTINCTキーワードのように動作します。

SELECT 
   DISTINCT department_id
FROM 
   employees
ORDER BY 
   department_id;Code language: SQL (Structured Query Language) (sql)

試してみる

GROUP BY句は、集約関数と組み合わせて使用するとより便利です。

たとえば、次のステートメントでは、GROUP BY句とCOUNT関数を使用して、部門ごとの従業員数をカウントします。

SELECT
	department_id,
	COUNT(employee_id) headcount
FROM
	employees
GROUP BY
	department_id;Code language: SQL (Structured Query Language) (sql)

試してみる

出力

+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         1 |
|             2 |         2 |
|             3 |         6 |
|             4 |         1 |
|             5 |         7 |
|             6 |         5 |
|             7 |         1 |
|             8 |         6 |
|             9 |         3 |
|            10 |         6 |
|            11 |         2 |
+---------------+-----------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

仕組み。

まず、GROUP BY句は、employeesテーブルの行を部門IDでグループ化します。
次に、COUNT(employee_id)は、各グループの従業員ID値の数を返します。

INNER JOINを使用したSQL GROUP BYの例

次の例では、部門ごとの従業員数を返します。また、INNER JOIN句を使用して、結果に部門名を含めます。

SELECT
	department_name,
	COUNT(employee_id) headcount
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
        department_name;Code language: SQL (Structured Query Language) (sql)

試してみる

出力

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Accounting       |         2 |
| Administration   |         1 |
| Executive        |         3 |
| Finance          |         6 |
| Human Resources  |         1 |
| IT               |         5 |
| Marketing        |         2 |
| Public Relations |         1 |
| Purchasing       |         6 |
| Sales            |         6 |
| Shipping         |         7 |
+------------------+-----------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

ORDER BYを使用したSQL GROUP BYの例

次の例では、ORDER BY句を使用して、部門を人数で並べ替えます。

SELECT 
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY department_name
ORDER BY headcount DESC;Code language: SQL (Structured Query Language) (sql)

試してみる

出力

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Shipping         |         7 |
| Sales            |         6 |
| Finance          |         6 |
| Purchasing       |         6 |
| IT               |         5 |
| Executive        |         3 |
| Marketing        |         2 |
| Accounting       |         2 |
| Human Resources  |         1 |
| Administration   |         1 |
| Public Relations |         1 |
+------------------+-----------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

ORDER BY句では、headcount エイリアスまたはCOUNT(employee_id)のいずれかを使用できることに注意してください。

HAVINGを使用したSQL GROUP BYの例

次の例では、HAVING句を使用して、人数が5を超える部門を見つけます。

SELECT 
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY department_name
HAVING headcount > 5
ORDER BY headcount DESC;Code language: SQL (Structured Query Language) (sql)

試してみる

出力

+-----------------+-----------+
| department_name | headcount |
+-----------------+-----------+
| Shipping        |         7 |
| Sales           |         6 |
| Finance         |         6 |
| Purchasing      |         6 |
+-----------------+-----------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

MIN、MAX、およびAVGを使用したSQL GROUP BYの例

次のクエリは、各部門の従業員の最低、最高、および平均給与を返します。

SELECT 
    department_name,
    MIN(salary) min_salary,
    MAX(salary) max_salary,
    ROUND(AVG(salary), 2) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;Code language: SQL (Structured Query Language) (sql)

試してみる

出力

+------------------+------------+------------+----------------+
| department_name  | min_salary | max_salary | average_salary |
+------------------+------------+------------+----------------+
| Accounting       |    8300.00 |   12000.00 |       10150.00 |
| Administration   |    4400.00 |    4400.00 |        4400.00 |
| Executive        |   17000.00 |   24000.00 |       19333.33 |
| Finance          |    6900.00 |   12000.00 |        8600.00 |
| Human Resources  |    6500.00 |    6500.00 |        6500.00 |
| IT               |    4200.00 |    9000.00 |        5760.00 |
| Marketing        |    6000.00 |   13000.00 |        9500.00 |
| Public Relations |   10000.00 |   10000.00 |       10000.00 |
| Purchasing       |    2500.00 |   11000.00 |        4150.00 |
| Sales            |    6200.00 |   14000.00 |        9616.67 |
| Shipping         |    2700.00 |    8200.00 |        5885.71 |
+------------------+------------+------------+----------------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

SUM関数を使用したSQL GROUP BYの例

部門ごとの総給与を取得するには、SUM関数をsalary列に適用し、次のようにdepartment_id列で従業員をグループ化します。

SELECT 
    department_name,
    SUM(salary) total_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;Code language: SQL (Structured Query Language) (sql)

試してみる

出力

+------------------+--------------+
| department_name  | total_salary |
+------------------+--------------+
| Accounting       |     20300.00 |
| Administration   |      4400.00 |
| Executive        |     58000.00 |
| Finance          |     51600.00 |
| Human Resources  |      6500.00 |
| IT               |     28800.00 |
| Marketing        |     19000.00 |
| Public Relations |     10000.00 |
| Purchasing       |     24900.00 |
| Sales            |     57700.00 |
| Shipping         |     41200.00 |
+------------------+--------------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

複数の列によるSQL GROUP BY

これまでは、すべての従業員を1つの列でグループ化する方法を見てきました。たとえば、次の句は、department_id列の同じ値を持つすべての行を1つのグループに配置します。

GROUP BY department_idCode language: SQL (Structured Query Language) (sql)

department_id列とjob_id列の両方の値で従業員をグループ化してみませんか？

GROUP BY department_id, job_idCode language: SQL (Structured Query Language) (sql)

この句は、department_id列とjob_id列の両方で同じ値を持つすべての従業員を1つのグループにグループ化します。

次のステートメントは、department_id列とjob_id列の両方で同じ値を持つ行を同じグループにグループ化し、これらの各グループの行を返します。

SELECT 
    department_name,
    job_title,
    COUNT(employee_id)
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
        INNER JOIN
    jobs j ON j.job_id = e.job_id
GROUP BY department_name , 
         job_title;Code language: SQL (Structured Query Language) (sql)

試してみる

+------------------+---------------------------------+--------------------+
| department_name  | job_title                       | COUNT(employee_id) |
+------------------+---------------------------------+--------------------+
| Accounting       | Accounting Manager              |                  1 |
| Accounting       | Public Accountant               |                  1 |
| Administration   | Administration Assistant        |                  1 |
| Executive        | Administration Vice President   |                  2 |
| Executive        | President                       |                  1 |
| Finance          | Accountant                      |                  5 |
| Finance          | Finance Manager                 |                  1 |
| Human Resources  | Human Resources Representative  |                  1 |
| IT               | Programmer                      |                  5 |
| Marketing        | Marketing Manager               |                  1 |
| Marketing        | Marketing Representative        |                  1 |
| Public Relations | Public Relations Representative |                  1 |
| Purchasing       | Purchasing Clerk                |                  5 |
| Purchasing       | Purchasing Manager              |                  1 |
| Sales            | Sales Manager                   |                  2 |
| Sales            | Sales Representative            |                  4 |
| Shipping         | Shipping Clerk                  |                  2 |
| Shipping         | Stock Clerk                     |                  1 |
| Shipping         | Stock Manager                   |                  4 |
+------------------+---------------------------------+--------------------+
19 rows in set (0.00 sec)Code language: PHP (php)

まとめ

GROUP BY句は、1つ以上の列の値に基づいて行をグループにグループ化します。
GROUP BY句で集約関数を使用して、各グループの集計値を計算します。

このチュートリアルは役に立ちましたか？