※学習中のため間違っている箇所があるかもしれません。
はじめに
今回は集計関数(GROUP BY/COUNT/SUM)を演習形式で解説していきます。
まずは、以下のSQLを実行し、テーブル作成、データ作成を行います。
テーブル、データの作成
注文テーブル
CREATE TABLE orders (
id int AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
price int NOT NULL,
purchase_date timestamp default current_timestamp,
user_id int NOT NULL
);
ユーザーテーブル
CREATE TABLE users (
id int AUTO_INCREMENT not null primary key,
name varchar(50),
email varchar(50),
role ENUM('USER', 'ADMIN'),
created_by varchar(50),
updated_by varchar(50),
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp,
delete_flg boolean
);
ユーザーテーブルデータ
INSERT INTO users (name) VALUES ('Taro');
INSERT INTO users (name) VALUES ('Hanako');
INSERT INTO users (name) VALUES ('Jiro');
INSERT INTO users (name) VALUES ('Miki');
注文テーブルデータ
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (1, 'コーラ', 1000, '2025-04-01 10:00:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (1, 'サイダー', 2000, '2025-04-02 11:30:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (2, 'デカビタ', 3000, '2025-04-01 09:00:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (3, 'ドデカミン', 1500, '2025-04-02 14:00:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (5, 'ZONE', 1200, '2025-04-03 16:00:00');
※incrementがうまくいかない場合は以下でAUTO_INCREMENTを初期化してください。
ALTER TABLE users AUTO_INCREMENT = 1;
演習問題
演習1 各ユーザーの合計購入金額を出力せよ（INNER JOIN + SUM）
要件：
- users と orders を INNER JOIN
- 各ユーザーの合計 price を算出
- 結果は「ユーザー名」「合計金額」として表示
- 金額が高い順で並べる
演習2 購入していないユーザーも含めて表示せよ（LEFT JOIN + COALESCE）
要件：
- users を左側にして LEFT JOIN
- 購入履歴がないユーザーは 合計金額 = 0 として表示（COALESCE使用）
- 「ユーザー名」「合計金額」を昇順で出力
演習3 日別の売上合計を求めよ（GROUP BY DATE）
要件：
- orders から purchase_date を DATE()で日付化
- 日別に SUM(price) を算出
- 「日付」「売上合計」として昇順に表示
演習4 2回以上購入したユーザーだけを抽出せよ（INNER JOIN + COUNT + HAVING）
要件：
- users と orders を INNER JOIN
- ユーザーごとの購入回数（COUNT(*)）をカウント
- 購入が「2回以上」のユーザーのみ抽出（HAVING 句）
演習5 orders にだけ存在するデータを抽出せよ（RIGHT JOIN + NULL検出）
要件：
- orders を右側にして RIGHT OUTER JOIN
- users.id IS NULL を条件に、orders 側だけに存在するレコードを抽出
- → user_id = 5（存在しないユーザー）を検出する問題
回答
演習1
演習1の条件は以下のSQLで満たすことができます。
SELECT users.name AS 'ユーザー名', MAX(users.id) AS 'ユーザーID', MAX(orders.user_id) AS '注文ID', SUM(orders.price) AS '価格'
FROM users INNER JOIN orders ON users.id=orders.user_id
group by users.name ORDER BY '価格' DESC;
解説
今回のSQL文を解説していきます。
試してみて、以下のエラーになった方がいると思います。
MySQL 5.7以降では sql_mode に ONLY_FULL_GROUP_BY がデフォルトで有効になっているため、
GROUP BY句に含まれないカラムをSELECTで使用する場合は集計関数で囲む必要があります。
#42000Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysqlstudy.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SQLの組み立て
まず、最初の条件を見てみましょう。
- users と orders を INNER JOIN
これを組み立てると以下のSQL文が完成します。
SELECT * FROM users INNER JOIN orders ON users.id=orders.user_id
実際に実行してみましょう。
こうすることでusersテーブルとordersテーブルを結合できました。
それでは次の要件を加えてみましょう。
- 各ユーザーの合計 price を算出
まず、合計のpriceを算出するためにはどうするかを考えます。
結合するだけでは合計値を取得することはできません。
そのため、グループ化する必要があります。
GROUP BYを使用してグループ化しましょう。
結合
SELECT users.name FROM users INNER JOIN orders ON users.id=orders.user_id GROUP BY users.name;
実行
このようにGROUP BYを使用することで、usersテーブルに存在するnameカラムが重複しているレコードをひとつにまとめることができました。
ここで思い出しておきたいのが、SQLの実行順序です。
SQLの実行順序は以下の通りです。
| 処理順序 | 句 |
|---|---|
| 1 | FROM |
| 2 | JOIN |
| 3 | WHERE |
| 4 | GROUP BY |
| 5 | HAVING |
| 6 | SELECT |
| 7 | ORDER BY |
| 8 | LIMIT |
JOINで結合した後にGROUP BYでグループ化します。
SQL文でも記載順序を間違えるとエラーになるため気を付けましょう。
グループ化
ここで最初のエラーを解説します。
意味としては「SELECTで指定したカラムが集計されていません」と説明しました。
なぜこのエラーが発生するかというと、
nameカラムでグループ化した場合、その他priceカラムは現在複数の値を持っていることになります。
先ほど実行したGROUP BYを付与する前後のSQLを見てみましょう。
Taroのように同じ名前の注文が複数ある場合、GROUP BYで1つにまとめられます。
このとき、他のカラム（例：price）は複数の値を持つため、集計関数を使わないとエラーになります。
現状、同じ名前でレコードをグループ化し、nameカラム以外のカラム情報を複数所持している状態になっているということです。
そのため、集計関数を使用せずにその他カラムを取得しようとすると、「どれを出力するかわかりません」といった状態になるということです。
実際に以下を実行してみるとエラーになるはずです。
SELECT users.name, orders.price FROM users INNER JOIN orders ON users.id=orders.user_id GROUP BY users.name;
このエラーは、どの値を使用するのかを明示的に指定すれば解決します。
SELECT users.name, SUM(price) FROM users INNER JOIN orders ON users.id=orders.user_id GROUP BY users.name;
この場合、「すべての値を合計」したものを出力します。
つまり、グループ化を行った場合、グループ化対象のカラム以外を出力したい際には、
どの値を指定し、出力するのかを明示的に記載する必要がある、ということです。
並び替えと別名
ここまででグループ化後に集計を行い、合計値を算出することができました。
最後にカラムを別名に変更し、ORDER BYで並び替えを行いましょう。
SELECT users.name, SUM(orders.price)
FROM users INNER JOIN orders ON users.id=orders.user_id
GROUP BY users.name;
別名
別名を付与したい場合、ASを使用します。
以下のように記載することでカラムに別名を付与して出力することができます。
SELECT カラム名 AS 別名 FROM テーブル名;
並び替え
並び替えはORDER BYを使用します。
以下の構文でORDER BYを使用することで、指定したカラムの順番で並び替えができます。
--降順
SELECT * FROM テーブル名 ORDER BY カラム名 DESC
--昇順
SELECT * FROM テーブル名 ORDER BY カラム名 ASC
完成
以下のSQL文で完成となります。
SELECT users.name AS 'ユーザー名', SUM(price) AS '合計金額'
FROM users INNER JOIN orders ON users.id=orders.user_id
GROUP BY users.name ORDER BY SUM(price) DESC;
一見難しいように見えても分解してみると、わかりやすくなると思うので、一度にすべての文を作成しようとせず、少しずつ組み立ててみましょう。
以下演習2以降も考え方自体は変わりません。
そのため、回答と補足のみ記載させていただきます
演習2
回答
SELECT users.name AS 'ユーザー名', COALESCE(SUM(orders.price), 0) AS '合計金額'
FROM users LEFT OUTER JOIN orders ON users.id=orders.user_id
GROUP BY users.name ORDER BY users.name ASC, SUM(orders.price) ASC;
※補足
COALESCEに関して
COALESCEは引数のうち、最初にNULLではない値が出現した場合、その値を返却します。
- COALESCE(NULL, NULL, 値1)：値1を返却
- COALESCE(NULL, 値1, 値2)：値1を返却
演習3
回答
SELECT DATE(purchase_date) AS '日付' , SUM(price) AS '合計金額' FROM orders GROUP BY DATE(purchase_date);
※補足
DATE()
日付または日付時間式の日付部分を抽出します。
演習4
回答
SELECT users.name AS 'ユーザー名', COUNT(orders.id) FROM users INNER JOIN orders ON users.id=orders.user_id GROUP BY users.name HAVING COUNT(orders.id)>=2;
※補足
COUNT()
GOUP BYなどでグループ化した際に、存在している行数を返却する。
演習5
SELECT orders.id AS '存在しないユーザーID'
from users RIGHT OUTER JOIN orders ON users.id=orders.user_id
WHERE users.id IS NULL
まとめ
今回はJOINを使用した演習問題を通じて
SQLの組み立て方や考え方を解説しました。
SQLで書き方がわからない、構文の意図がつかめない、
といった人の参考になれば幸いです。
次回はサブクエリの学習を進めていきます。
引き続き、SQLの基礎力を高める学習記事を投稿していきます！
[前：https://qiita.com/suica1010/items/ce76be2580faec9689d4](SQL学習入門 ｜ INNER JOINとOUTER JOINの違いを実例で解説)