MySQLの集約関数と非集約列に関するエラーとその対処法

対象者

• MySQLを使用してデータベースの操作やクエリの実行を行っている方
• only_full_group_byエラーに遭遇したことがある、または遭遇する可能性がある方
• SQLの基本的な知識を持っている方

開発環境:

MySQL Ver 8.1.0

前提

データベース作成:
以下の手順でデータベースとテーブルを作成し、データを挿入します。

db作成

CREATE DATABASE test_db;
USE test_db;

table作成

CREATE TABLE users (
    id INT PRIMARY KEY,
    family_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    nickname VARCHAR(255),
    prefecture VARCHAR(10) NOT NULL,
    phone_number VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    last_logged_in DATE,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

データの挿入

TRUNCATE TABLE users; // 指定されたテーブルのすべてのレコードを高速に削除

INSERT INTO test_db.users VALUES (1, '山田', '太郎', 'やまぽん', '東京都', '09011111111', 22, '2022-01-02', '2022-01-01 12:01:11', '2022-01-01 12:01:11');
INSERT INTO test_db.users VALUES (2, '田中', '次郎', 'たなか', '東京都', '09022222222', 30, '2022-04-01', '2022-03-02 10:53:20', '2022-04-01 20:31:47');
INSERT INTO test_db.users VALUES (3, '佐藤', '隆', 'たかくん', '大阪府', '09033333333', 18, '2022-05-23', '2022-03-01 21:23:55', '2022-05-11 16:02:13');
INSERT INTO test_db.users VALUES (4, '松本', '健斗', NULL, '宮崎県', '09044444444', 35, '2022-01-02', '2021-11-21 14:05:42', '2022-01-02 17:52:12');
INSERT INTO test_db.users VALUES (5, '坂本', '太郎', 'たろくん', '滋賀県', '09055555555', 17, '2021-12-23', '2021-06-02 23:41:44', '2021-12-10 09:47:29');
INSERT INTO test_db.users VALUES (6, '飯島', '隆平', NULL, '東京都', '09066666666', 24, '2022-01-04', '2021-12-31 10:20:11', '2022-01-04 21:00:48');
INSERT INTO test_db.users VALUES (7, '藤本', '翔也', 'しょう', '東京都', '09077777777', 44, '2022-05-01', '2022-03-24 06:01:53', '2022-05-01 04:01:02');
INSERT INTO test_db.users VALUES (8, '山本', '圭佑', 'やまっち', '北海道', '09077777777', 39, '2022-03-16', '2021-09-03 19:56:39', '2022-03-16 09:42:56');
INSERT INTO test_db.users VALUES (9, '服部', '誠司', 'はっとり', '鹿児島県', '09099999999', 29, '2022-06-29', '2022-01-02 06:47:22', '2022-06-29 06:35:43');
INSERT INTO test_db.users VALUES (10, '城田', '浩二', 'こうくん', '石川県', '09011112222', 20, '2022-04-14', '2021-12-20 21:01:24', '2022-04-13 10:37:52');
INSERT INTO test_db.users VALUES (11, '本田', '光輝', NULL, '大阪府', '09022223333', 22, '2022-04-01', '2022-01-01 12:01:10', '2022-04-01 21:45:33');
INSERT INTO test_db.users VALUES (12, '今本', '修平', 'しゅうちゃん', '東京都', '09033334444', 30, '2022-01-01', '2021-11-08 11:39:52', '2022-01-01 06:01:56');
INSERT INTO test_db.users VALUES (13, '中島', '優希', 'ゆうくん', '北海道', '09044445555', 31, '2022-04-01', '2021-10-29 12:37:48', '2022-04-01 08:11:05');

エラー内容の詳細と解決策

MySQLのクエリ実行時に以下のようなエラーメッセージが表示される場合があります。

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test_db.users.prefecture'; this is incompatible with sql_mode=only_full_group_by

原因:

このエラーは、MySQLのonly_full_group_byモードが有効になっているときに、GROUP BY句を使用せずに集約関数と非集約列を組み合わせてクエリを実行した場合に発生します。このモードは、非予測可能な結果を避けるために導入されています。

具体的には、以下のようなクエリでエラーが発生します。

SELECT prefecture, AVG(age) FROM users;

ここで、prefectureは非集約列、AVG(age)は集約関数ですが、GROUP BY句がないためエラーが発生します。

解決策

このエラーを解消するためには、以下のようにGROUP BY句を追加して、非集約列をグループ化する必要があります。

SELECT
    prefecture,
    AVG(age) AS average_age
FROM
    users
GROUP BY
    prefecture;

上記のクエリでは、prefectureごとにageの平均値を計算しています。

また、HAVINGを用いる場合は順序を間違わないように行います。

SELECT
	prefecture, AVG(age) AS average_age
FROM
	users
WHERE
	prefecture IN ('東京都', '大阪府')
GROUP BY
	prefecture 
HAVING 
	average_age <= 20;