Column must appear in GROUP BY clause or aggregate function

1
2
3
4
5
6

-- NG: nameがGROUP BYに含まれていない
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

-- エラー: name must appear in GROUP BY clause

1
2
3
4

-- OK: nameもGROUP BYに追加
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department, name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- OK: MAX/MIN/ANY_VALUE等で1つの値を選択
SELECT department, MAX(name) AS sample_name, COUNT(*)
FROM employees
GROUP BY department;

-- OK: 名前をカンマ区切りで連結（PostgreSQL）
SELECT department, STRING_AGG(name, ', ') AS names, COUNT(*)
FROM employees
GROUP BY department;

-- OK: 名前をカンマ区切りで連結（MySQL）
SELECT department, GROUP_CONCAT(name) AS names, COUNT(*)
FROM employees
GROUP BY department;

1
2
3
4

-- OK: 不要なカラムを削除
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 各部署の最初の従業員を取得
SELECT e.department, e.name, dept_counts.cnt
FROM employees e
INNER JOIN (
    SELECT department, COUNT(*) AS cnt
    FROM employees
    GROUP BY department
) dept_counts ON e.department = dept_counts.department
WHERE e.id = (
    SELECT MIN(id) FROM employees e2
    WHERE e2.department = e.department
);

1
2
3

-- 各部署の従業員と、その部署の人数を表示
SELECT department, name, COUNT(*) OVER (PARTITION BY department) AS dept_count
FROM employees;

1
2
3
4
5

-- MySQLでモードを確認
SELECT @@sql_mode;

-- ONLY_FULL_GROUP_BY が含まれていると厳格モード
-- 含まれていないと、非集約カラムは任意の値が返る（非推奨）

1
2
3
4
5
6
7

-- 一時的にモードを変更（非推奨）
SET sql_mode = '';

-- これで動くが、結果は不定
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

1
2
3
4

-- どの値でもよい場合
SELECT department, ANY_VALUE(name) AS sample_name, COUNT(*)
FROM employees
GROUP BY department;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- NG: 各ユーザーの最新注文を取得しようとして失敗
SELECT user_id, MAX(created_at), order_id
FROM orders
GROUP BY user_id;  -- order_idがエラー

-- OK: サブクエリで最新のorder_idを取得
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT user_id, MAX(created_at) AS max_created
    FROM orders
    GROUP BY user_id
) latest ON o.user_id = latest.user_id
        AND o.created_at = latest.max_created;

-- OK: ウィンドウ関数を使用（PostgreSQL/MySQL 8.0+）
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) ranked
WHERE rn = 1;

1
2
3
4
5
6
7
8
9

-- 各商品の売上と、その商品の全注文を表示
SELECT
    p.product_id,
    p.name,
    o.order_id,
    o.quantity,
    SUM(o.quantity) OVER (PARTITION BY p.product_id) AS total_quantity
FROM products p
JOIN orders o ON p.product_id = o.product_id;