SQLの集計結果が合わないときに確認する5項目

注文表では4,500円なのに、明細表をJOINすると7,500円になった。SQLはエラーなく動くため、どこを直すべきか迷う場面です。SQLの集計結果が合わないときは、合計式より先に、計算へ渡した行を確認します。

同じ注文が増えていないか、NULLで計算から外れた行がないかを、小さな表で追います。確認する順番は、JOINの重複、NULL、集計単位、条件を置く場所、COUNTの数え方です。

この記事では架空の注文4件を共通の例にし、5項目の原因と修正後の数値を比べます。例はSQLiteで実行しました。Oracleでは未実行のため、公式文書による仕様の確認と、実際の実行結果を分けて示します。

練習用の注文4件で、基準の金額を確かめる

前提はSELECTとWHEREの基本です。読み方があやふやなら、SELECTとWHEREの基礎ドリルで短い検索から試せます。金額の単位は円で、税・返品・通貨換算は今回の例に含めません。

demo_ordersは注文1件につき1行の表です。demo_itemsは注文に属する明細の一覧です。注文101と103には明細が2行あり、注文104には明細がない状態を用意しました。実際の顧客や売上は使っていません。

注文ID	店舗	注文金額	値引き額
101	A	1,000	100
102	A	1,000	NULL
103	B	2,000	0
104	B	500	50

新しい練習用データベースで、次のSQLを1回実行します。既存の表を削除する処理はありません。同じ名前の表がある環境では実行せず、空の練習環境を使ってください。

CREATE TABLE demo_orders (
  order_id INTEGER PRIMARY KEY,
  store_code VARCHAR(1) NOT NULL,
  sales_amount INTEGER NOT NULL,
  discount_amount INTEGER
);
CREATE TABLE demo_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES demo_orders(order_id)
);
INSERT INTO demo_orders VALUES (101, 'A', 1000, 100);
INSERT INTO demo_orders VALUES (102, 'A', 1000, NULL);
INSERT INTO demo_orders VALUES (103, 'B', 2000, 0);
INSERT INTO demo_orders VALUES (104, 'B', 500, 50);
INSERT INTO demo_items VALUES (1, 101);
INSERT INTO demo_items VALUES (2, 101);
INSERT INTO demo_items VALUES (3, 102);
INSERT INTO demo_items VALUES (4, 103);
INSERT INTO demo_items VALUES (5, 103);

SELECT COUNT(*) AS order_count, SUM(sales_amount) AS total
FROM demo_orders;

実行結果はorder_countが4、totalが4500です。この基準値を書き留めてから、各例を実行します。総額だけでなく、何件を足した数字なのかを一緒に確認するためです。

1．JOINの前後で、同じ注文が増えていないか

注文金額を足すなら、注文1件を1回だけ足す形にそろえます。JOINは、別の表の行を条件で組み合わせる処理です。1件の注文に明細が2件対応すれば、結合後には同じ注文金額が2回現れます。

SELECT o.order_id, o.sales_amount, i.item_id
FROM demo_orders o
LEFT JOIN demo_items i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;

結合後は6行です。注文101の1,000円と注文103の2,000円が、それぞれ2行あります。LEFT JOINは左の注文を残すため、明細のない注文104も1行残ります。

SELECT COUNT(*) AS joined_rows,
       SUM(o.sales_amount) AS total,
       SUM(DISTINCT o.sales_amount) AS distinct_total
FROM demo_orders o
LEFT JOIN demo_items i ON i.order_id = o.order_id;

実行結果はjoined_rowsが6、totalが7500、distinct_totalが3500です。SUM(DISTINCT …)は注文IDを見ず、同じ金額を1回だけ足します。正しい4,500円にならないため、重複対策として無条件に付けないでください。

明細の情報も必要なら、明細側を注文IDごとに1行へまとめてから結合できます。次の例は明細数を先に数えます。注文側の行が増えなくなり、totalは4500です。売上合計だけが目的なら、注文表だけの集計で足ります。

SELECT SUM(o.sales_amount) AS total
FROM demo_orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM demo_items
  GROUP BY order_id
) i ON i.order_id = o.order_id;

結合は条件に合う行の組を作る処理です。仕様はOracle公式のJoinsで確認できます。結合キーに期待した一意性があるかも調べましょう。

2．NULLを含む計算が、合計から外れていないか

NULLは0ではありません。値がない、または分からない状態を表します。数値からNULLを引くと計算結果もNULLになり、SUMはその結果を足しません。

SELECT SUM(sales_amount - discount_amount) AS total
FROM demo_orders;

totalは3350です。注文102の式がNULLになり、1,000円分が合計から外れました。NULLを「値引きなし」と扱うルールなら、最初のNULLでない値を返すCOALESCEを使えます。

SELECT SUM(sales_amount - COALESCE(discount_amount, 0)) AS total
FROM demo_orders;

この場合のtotalは4350です。0へ置き換える場所は、SUMの外側ではなく、引き算に使う値引き額です。合計が出たあとでNULLを0へ変えても、途中で落ちた注文金額は戻りません。

NULLを含む計算はOracle公式のNulls、補完の仕様はCOALESCEで確認できます。

3．GROUP BYで、求めたい単位より細かく分けていないか

結果の1行を「店舗1つ」にしたいなら、店舗ごとにまとめます。GROUP BYは、指定した列の値が同じ行を1組にする指定です。注文IDまで加えると、同じ店舗でも注文ごとに別の組になります。

SELECT store_code, SUM(sales_amount) AS total
FROM demo_orders
GROUP BY store_code
ORDER BY store_code;

店舗	total
A	2000
B	2500

このSQLのGROUP BYへorder_idを加えると、店舗Aは1,000円ずつの2組に分かれます。店舗Bも2,000円と500円の2組です。構文を直す前に、「結果の1行が何を表すか」を言葉にしておくと判断できます。

Oracleでは、集計した列と一緒に表示する通常の列は、GROUP BYの指定との対応が必要です。SQLiteは集計対象でもGROUP BY指定でもない列を許します。SQLiteで動いたことだけでOracleの構文も正しいとは判断できません。

4．WHEREとHAVINGで、除外する対象を取り違えていないか

注文を選ぶ条件はWHERE、集計した店舗を選ぶ条件はHAVINGに書きます。「1,500円以上の注文」と「合計が1,500円以上の店舗」は、対象が異なります。

SELECT store_code, SUM(sales_amount) AS total
FROM demo_orders
WHERE sales_amount >= 1500
GROUP BY store_code
ORDER BY store_code;

この結果は店舗B、totalが2000です。条件を満たす注文103だけを集計した数字です。求めるものが「店舗の合計が1,500円以上」なら、次のように書きます。

SELECT store_code, SUM(sales_amount) AS total
FROM demo_orders
GROUP BY store_code
HAVING SUM(sales_amount) >= 1500
ORDER BY store_code;

結果は店舗Aが2000、店舗Bが2500です。説明上は「行を選ぶ→組に分けて集計する→組を選ぶ」と追います。これは結果を考える順番であり、データベース内部の物理的な実行順を断定するものではありません。

5．COUNTで、行・入力済みの値・種類のどれを数えたか

COUNT(*)は行数、COUNT(列)はNULLでない値の数です。COUNT(DISTINCT 列)はNULLを除いて値の種類を数えます。何を1件と呼ぶかによって、正しい式が変わります。

SELECT COUNT(*) AS all_rows,
       COUNT(discount_amount) AS known_discounts,
       COUNT(DISTINCT store_code) AS stores
FROM demo_orders;

式	結果	数えているもの
COUNT(*)	4	注文の行数
COUNT(discount_amount)	3	値引き額がNULLでない行数
COUNT(DISTINCT store_code)	2	店舗コードの種類数

SELECT o.order_id, COUNT(*) AS joined_rows,
       COUNT(i.item_id) AS item_count
FROM demo_orders o
LEFT JOIN demo_items i ON i.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

注文104ではjoined_rowsが1、item_countが0です。明細がなくてもLEFT JOINが残した行をCOUNT(*)は数えます。COUNT(i.item_id)なら、NULLとして補われた明細IDを除外できます。

注文数を数える場合も、結合後の行数と同じとは限りません。COUNT(DISTINCT o.order_id)は注文IDの種類数を数えます。ただし、件数が直っても、同じSELECT内のSUMの重複まで直るわけではありません。Oracle公式のCOUNTにも、NULLと重複の扱いが示されています。

SQL学習の研究でも、JOINと集計はつまずきやすい

2023年のAlkhabazらの研究は、学生が問い合わせ言語のどこでつまずくかを調べました。2022年秋の受講者462人による、35万7,215件を超える課題提出を分析しています。対象はSQLだけでなく、MongoDBとNeo4jも含みます。

研究では、集計とJOINが特に難しい操作として挙がっています。これは課題提出の分析であり、この記事の5項目を使うと学習効果が上がると実験で示したものではありません。特定の授業の結果を、すべての職場へそのまま当てはめることもできません。

本記事では、この難所を注文4件へ縮め、行がどう変わるかを追える構成にしました。研究内容はイリノイ大学の論文紹介で確認できます。

SQLの集計結果が合わないときのFAQ

次の30分で、店舗別の件数と合計を確かめる

練習として、合計が2,000円以上の店舗について、注文件数と売上合計を出してください。最初の10分で表を作り、次の10分で自分のSQLを書きます。残りの10分で、結果の各行を手計算と比べます。

答えは店舗Aが2件・2,000円、店舗Bが2件・2,500円です。まず自分で試してから、次のSQLと比べてください。

SELECT store_code, COUNT(*) AS order_count,
       SUM(sales_amount) AS total
FROM demo_orders
GROUP BY store_code
HAVING SUM(sales_amount) >= 2000
ORDER BY store_code;

表の設計も見直したいときは、主キー・外部キー・NOT NULLなどの制約へ進めます。注文IDの重複を許さないことと、1件の注文に複数の明細があることは両立します。

公式資料：Aggregate Functions（GROUP BY・HAVING・NULL・DISTINCT）、COUNT、Joins。