グループ化

2024 4/20

2024.04.20 2024.04.20

記事の内容

概要

GROUP BY句は、データを特定の列の値でグループ化するためのSQL構文です。
※特定の条件に基づいてデータを集計したり、結果をサマリーしたりすることが可能

GROUP BY

記述

GROUP BY カラム名

mysql> SELECT * FROM sales;
+----+------------+------------+
| id | product_id | sale_price |
+----+------------+------------+
|  1 |          1 |       1000 |
|  2 |          2 |       2000 |
|  3 |          2 |       2000 |
|  4 |          2 |       2000 |
|  5 |          1 |       1000 |
+----+------------+------------+
5 rows in set (0.00 sec)

【グルーピングと合計】
mysql> SELECT product_id, SUM(sale_price) AS total_sales
    -> FROM sales
    -> GROUP BY product_id;
+------------+-------------+
| product_id | total_sales |
+------------+-------------+
|          1 |        2000 |
|          2 |        6000 |
+------------+-------------+
2 rows in set (0.00 sec)

集計結果を絞り込む

HAVING

記述

GROUP BY カラム名 HAVING 条件式;

mysql> SELECT * FROM sales;
+----+------------+------------+
| id | product_id | sale_price |
+----+------------+------------+
|  1 |          1 |       1000 |
|  2 |          2 |       2000 |
|  3 |          2 |       2000 |
|  4 |          3 |       3000 |
|  5 |          2 |       2000 |
|  6 |          1 |       1000 |
+----+------------+------------+
5 rows in set (0.00 sec)

【グルーピングと合計】
mysql> SELECT product_id, SUM(sale_price) AS total_sales
    -> FROM sales
    -> GROUP BY product_id;
+------------+-------------+
| product_id | total_sales |
+------------+-------------+
|          1 |        2000 |
|          2 |        6000 |
|          3 |        3000 |
+------------+-------------+
2 rows in set (0.00 sec)

【集計結果後に絞り込み】
mysql> SELECT product_id, SUM(sale_price) AS total_sales
    -> FROM sales
    -> GROUP BY product_id HAVING SUM(sale_price) > 5000;
+------------+-------------+
| product_id | total_sales |
+------------+-------------+
|          2 |        6000 |
+------------+-------------+
1 row in set (0.00 sec)