13. 集計

オンライン教材 > MySQL超入門

GROUP BY

GROUP BYとは

SQLのGROUP BYは、データを特定の列の値でグループ化するために使用されます。GROUP BYを使用することで、レコードのデータをグループにまとめることができます。

GROUP BY カラム1, カラム2, ...

集計で不整合しない

GROUP BYは通常、集計関数（SUM、COUNT、AVGなど）と一緒に使用します。データをグループ化して集計し、結果を返します。

SELECT カラム1, カラム2, ..., 集計関数(カラム1)
FROM テーブル名

集計するカラムは一致させる

集計するためには、SELECTで選択するカラム（カラム1、カラム2...）や集計関数のカラムは、GROUP BYで指定したカラムと不整合が起きないように指定します。

SELECT カラム1, カラム2, 集計関数(カラム1)
FROM テーブル名
GROUP BY カラム1, カラム2

GROUP BYの利用方法

「user_items」テーブルに以下のレコードがあったとします。

id user_id item_id amount total_price
1 1 1 3 900
2 2 1 1 300
3 1 3 2 800
4 3 2 2 700

ユーザごとに集計

「user_items」テーブルのレコードを「user_id」別に集計してみます。

SELECT 
  user_id,
  COUNT(id) AS order_count
  FROM user_items
GROUP BY user_id;

ユーザごとに購入した回数が表示できました。

結果
user_id	order_count
1 2
2 1
3 1
SQL
SELECT
  user_id, 
  COUNT(id) AS order_count, 
  SUM(total_price) AS total_price
FROM user_items
GROUP BY user_id;

ユーザごとに購入した回数と合計金額を集計表示できました。

結果
user_id	order_count total_price
1 2 1700
2 1 300
3 1 700

商品ごとに集計

「user_items」テーブルのレコードを「item_id」別に集計してみます。

SQL
SELECT 
  item_id, 
  COUNT(id) AS order_count, 
  SUM(amount) AS order_amount,
  SUM(total_price) AS total_price
FROM user_items
GROUP BY item_id;

商品ごとに購入された個数が集計して表示できました。

結果
item_id order_count order_amount total_price	
1 2 4 1200
2 1 2 700
3 1 2 800

JOINで処理

JOINで結合する場合、GROUP BYで集計に不整合がなければ、GROUP BYの指定カラム以外もSELECTできます。

SELECT 
  table1.column1, 
  table1.column2, 
  table2.column1,
  table2.column2
  集計関数(table1.column1)
FROM table1
JOIN table2 ON table1.column1 = table2.column1
GROUP BY table1.column1, table1.column2;
  • JOINでは、カラム名が重複しないように「テーブル名.カラム名」で指定するとよいでしょう

ユーザごとに集計

「user_items」と「users」をリレーションして、ユーザ名を表示した例です。

SQL
SELECT
  user_items.user_id, 
  users.name,
  COUNT(user_items.id) AS order_count, 
  SUM(user_Items.amount) AS order_amount,
  SUM(user_items.total_price) AS total_price
FROM user_items
JOIN users ON user_items.user_id = users.id
GROUP BY user_items.user_id;
結果
user_id	name order_count order_amount total_price	
1 三宅 直子 2 5 1700
2 渡辺 直人 1 1 300
3 三宅 聡太郎 2 2 700

商品ごとに集計

「user_items」と「items」をリレーションして、商品名を表示した例です。

SQL
SELECT
  user_items.item_id, 
  items.name,
  COUNT(items.id) AS order_count,
  SUM(user_Items.total_price) AS total_price
FROM user_items
JOIN items ON user_items.item_id = items.id
GROUP BY user_items.item_id;
結果
item_id	name order_count total_price	
1 コーヒー 2 1200
2 紅茶 1 700
3 ほうじ茶 1 800

HAVING

HAVINGとは

HAVINGは、GROUP BYと一緒に使用して、グループ化されたデータに対して条件を適用するためのSQLです。HAVINGで特定の条件を満たすグループのみを結果として取得することができます。

HAVING 条件

HAVINGの基本

GROUP BYの後にHAVINGで条件指定します。条件には集計関数も利用できます。

SELECT カラム1, カラム2, 集計関数(カラム1)
FROM テーブル名
GROUP BY カラム1, カラム2
HAVING 条件

ユーザの購入合計金額を条件指定

合計金額が1000円以上購入したユーザを検索する例です。SUM() で合計金額を計算した結果をHAVINGの条件指定します。

SQL
SELECT
  user_items.user_id, 
  users.name,
  COUNT(user_items.id) AS order_count, 
  SUM(user_Items.amount) AS item_amount,
  SUM(user_items.total_price) AS total_price
FROM user_items
JOIN users ON user_items.user_id = users.id
GROUP BY user_items.user_id
HAVING SUM(user_Items.total_price) >= 1000;

合計金額が1000円以上購入したユーザが表示されました。

結果
user_id name order_count item_amount total_price
1 三宅 直子 2 5 1700

商品の購入合計金額を条件指定

合計金額が1000円以上購入された商品検索する例です。SUM() で合計金額を計算した結果をHAVINGの条件指定します。

SQL
SELECT
  user_items.item_id, 
  items.name,
  COUNT(items.id) AS order_count,
  SUM(user_Items.total_price) AS total_price
FROM user_items
JOIN items ON user_items.item_id = items.id
GROUP BY user_items.item_id
HAVING SUM(user_Items.total_price) >= 1000;

合計金額が1000円以上購入された商品が表示されました。

結果
item_id name order_count total_price
1 コーヒー 2 1200

WHEREの利用

WHEREで条件指定する場合は、GROUP BYの前に記述します。

SELECT 
  table1.column1
FROM table1
JOIN table2 ON table1.column1 = table2.column1
WHERE 条件
GROUP BY table1.column1;

特定の商品を除いた集計

「item_id」が「2」以外で、合計金額が500円以上購入された商品検索する例です。

SQL
SELECT
  user_items.item_id, 
  items.name,
  COUNT(items.id) AS order_count,
  SUM(user_Items.total_price) AS total_price
FROM user_items
JOIN items ON user_items.item_id = items.id
WHERE user_items.item_id != 2
GROUP BY user_items.item_id
HAVING SUM(user_Items.total_price) >= 500;

「item_id」「2」以外の、合計金額が500円以上購入された商品が表示されました。

結果
item_id name order_count total_price
1 コーヒー 2 1200
3 ほうじ茶 1 800
<< 結合

MySQL超入門