SQLのCASE WHENで区分ごとに集計する方法｜複数条件・ELSE・NULLの注意点

売上を金額で「大口・中口・小口」に分けて、件数と合計を出したい。SQLのCASE WHENで書いてみたら、大口が1件も出てこない。区分名が空の行が混ざる。条件を2つにしたら、件数が多すぎる。

最初に確認したいのは、区分ごとの件数と、金額の最小・最大です。

この記事でわかること

CASE WHENで区分を作り、GROUP BY・SUMで集計する書き方
件数・合計がずれる4つの原因と直し方
区分の条件をSQLに残すと、毎月の集計の何が良くなるか

結論：CASE WHENで区分を作り、件数と最小・最大で確かめる
まずここから確認：例の売上表に、1行ずつ区分を付ける
区分ごとに集計する：GROUP BYで縦に、SUMで横に並べる
件数・合計がずれる原因4つ：順番・ELSE・ANDとOR・NULL
それでも合わないとき：条件に使った列の値を一覧にする
業務への着地：区分の境目をSQLに残し、毎月同じ基準で数える
まとめ
次に読む記事

結論：CASE WHENで区分を作り、件数と最小・最大で確かめる

区分ごとの集計は、CASE WHENで1行ずつ区分を付け、GROUP BYかSUMでまとめます。

CASE WHENは、条件に応じて値を返す式です。上から順に条件を見て、最初に当たった値を返します。どの条件にも当たらない行は、ELSEがなければNULL(値なし)になります。

条件の順番や範囲を間違えても、エラーは出ません。区分の数字だけがずれます。そのため、集計したら次の2つを確かめます。

区分の件数を足すと、元の表の件数と合うか
区分ごとの最小・最大が、条件の範囲に収まっているか

まずここから確認：例の売上表に、1行ずつ区分を付ける

集計の前に1行ずつ区分を付けて見ると、CASE WHENがどの行をどの区分に入れたかが分かります。

この記事では、架空の売上表(sales)を例にします。1行が1件の注文で、amountは金額(円)です。

order_id	channel	amount
1	店舗	12000
2	EC	5000
3	EC	15000
4	店舗	800
5	電話	3000

channelは、注文を受けた窓口です。全部で5件、合計35,800円です。

金額が1万円以上を「大口」、3千円以上を「中口」とし、それ以外を「小口」にします。この記事のSQLは、PostgreSQLとMySQL 8.0以降を想定しています。

SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 10000 THEN '大口'
    WHEN amount >= 3000 THEN '中口'
    ELSE '小口'
  END AS band
FROM sales;

書き方の骨組みは、次のとおりです。

WHEN 条件 THEN 値：条件に当たったら、その値を返す
ELSE 値：どの条件にも当たらないときの値
END AS 名前：CASEの終わりと、作る列の名前

実行結果

order_id	amount	band
1	12000	大口
2	5000	中口
3	15000	大口
4	800	小口
5	3000	中口

この結果では、5000円の注文が1つ目の条件に当たらず、2つ目の条件で「中口」になっています。

2つ目の条件に「1万円未満」を書かなくてよいのは、上から順に見るからです。1つ目に当たった行は、2つ目の条件まで進みません。

条件を上から順に判定していく形が、地味に好きな書き方です。

区分ごとに集計する：GROUP BYで縦に、SUMで横に並べる

区分を行に並べるならGROUP BY、列に並べるならSUMの中にCASE WHENを書きます。

GROUP BYで、区分ごとの件数と合計を出す

区分を付けるSQLをWITH句でまとめ、外側でGROUP BYします。WITH句は、SQLの中で一時的な表に名前をつける書き方です。

WITH classified AS (
  SELECT
    amount,
    CASE
      WHEN amount >= 10000 THEN '大口'
      WHEN amount >= 3000 THEN '中口'
      ELSE '小口'
    END AS band
  FROM sales
)
SELECT
  band,
  COUNT(*) AS orders,
  SUM(amount) AS total_amount
FROM classified
GROUP BY band;

実行結果

band	orders	total_amount
大口	2	27000
中口	2	8000
小口	1	800

件数を足すと5件、合計は35,800円で、元の表と合います。

並び順は、ORDER BYを付けないと決まりません。

WITH句を使うのは、区分の条件を1か所に書くためです。GROUP BYに同じCASE WHENをもう一度書く方法もあります。SELECTで付けた名前(band)をGROUP BYで使えるかは、DBによって違います。

SUMの中にCASE WHENを書いて、窓口ごとの売上を横に並べる

窓口ごとの売上を1行に並べるときは、SUMの中にCASE WHENを書きます。

SELECT
  SUM(CASE WHEN channel = '店舗'
      THEN amount ELSE 0 END) AS store_sales,
  SUM(CASE WHEN channel = 'EC'
      THEN amount ELSE 0 END) AS ec_sales,
  SUM(CASE WHEN channel = '電話'
      THEN amount ELSE 0 END) AS phone_sales
FROM sales;

実行結果

store_sales	ec_sales	phone_sales
12800	20000	3000

3つを足すと35,800円で、元の表と合います。

窓口に当たる行はamountを、当たらない行は0を足しています。件数にしたいときは、THEN amount を THEN 1 にします。店舗なら2件です。

件数を数えるときは、COUNTとELSE 0を組み合わせない。COUNTは、NULL以外の値を全部数えます。0もNULLではないので、次の式は全件の5になります。

COUNT(CASE WHEN channel = '店舗' THEN 1 ELSE 0 END)

COUNTを使うなら、ELSEを書かずに、当たらない行をNULLにします。

区分を確かめる：件数と最小・最大を出す

区分ごとの最小・最大を出すと、条件の範囲からはみ出した行に気づけます。GROUP BYのSQLの外側のSELECTに、MIN(amount) と MAX(amount) を足します。

例の表では、大口は12,000〜15,000円です。中口は3,000〜5,000円、小口は800円で、どれも条件の範囲に収まっています。

件数・合計がずれる原因4つ：順番・ELSE・ANDとOR・NULL

CASE WHENの数字がずれるときは、条件の順番・ELSE・ANDとOR・NULLの4つに分けて探します。

起きていること	疑う原因	最初に見る場所
大口が出ない・中口の最大が1万円以上	1. 条件の順番	WHENの並び
区分名が空(NULL)の行が出る	2. ELSEがない	CASEの最後
条件を足した区分に、条件外の行が入る	3. ANDとOR	WHENの中のかっこ
値が空の行が、別の区分に入る	4. NULLの判定	`= NULL` の有無

1〜3は、件数と最小・最大を出すと見つかります。4は、条件に使う列に空の値があるかを先に数えます。

原因1：広い条件を上に書いている

問題と原因：CASE WHENは、最初に当たった条件の値を返し、残りの条件は見ません。広い条件(3千円以上)を上に書くと、1万円以上の行もそこで止まります。

CASE
  WHEN amount >= 3000 THEN '中口'
  WHEN amount >= 10000 THEN '大口'
  ELSE '小口'
END AS band

区分ごとの件数と合計は、次のようになります。

band	orders	total_amount
中口	4	35000
小口	1	800

この結果では、大口の2件が中口に入っています。件数の合計は5件のままなので、件数を足すだけでは気づけません。

確認方法：区分ごとの最小・最大を出します。中口の最大が15,000円になり、1万円以上の行が入っていると分かります。

どう直すか：狭い条件(1万円以上)から順に書きます。上の条件に当たらなかった行だけが、次の条件に進みます。

原因2：ELSEを書いていない

問題と原因：どのWHENにも当たらない行は、ELSEがないとNULLになります。小口をELSEで付けるつもりで書き忘れると、800円の注文が区分から漏れます。

CASE
  WHEN amount >= 10000 THEN '大口'
  WHEN amount >= 3000 THEN '中口'
END AS band

区分ごとの件数と合計は、次のようになります。

band	orders	total_amount
大口	2	27000
中口	2	8000
NULL	1	800

GROUP BYでは、NULLの行も1つの区分としてまとまります。

報告書(会議や上司に出す集計表)に大口と中口だけを写すと、1件・800円が抜けます。

確認方法：GROUP BYの結果に、区分名が空(NULL)の行がないかを見ます。

どう直すか：CASEの最後にELSEを書き、どの条件にも当たらない行の行き先を決めます。小口のように意味のある区分か、「その他」にします。

原因3：ANDとORを混ぜて、かっこを付けていない

問題と原因：WHENの中では、ANDやORで2つ以上の条件をつなげます。ANDとORを混ぜると、ANDが先に計算されます。

例として、店舗と電話の注文のうち、1万円以上を「店舗・電話の大口」にします。

CASE
  WHEN channel = '店舗' OR channel = '電話'
    AND amount >= 10000
    THEN '店舗・電話の大口'
  ELSE 'その他'
END AS band

区分ごとの件数と合計は、次のようになります。

band	orders	total_amount
店舗・電話の大口	2	12800
その他	3	23000

この条件は「店舗の注文すべて」または「電話の1万円以上」と読まれ、800円の店舗の注文が大口に入っています。

確認方法：区分ごとの最小・最大を出します。店舗・電話の大口の最小が、800円になります。

どう直すか：ORでつないだ部分を、かっこでくくります。

WHEN (channel = '店舗' OR channel = '電話') AND amount >= 10000

直すと、店舗・電話の大口は1件・12,000円になります。

私もANDとORのかっこがずれて、件数が合わなかったりエラーが出たりしたことがあります。

条件が2つの列にまたがるときは、CASEの中にCASEを書く(入れ子)より、ANDでつなげば1段で書けます。

原因4：空(NULL)の判定を「=」で書いている

問題と原因：NULLとの「=」の比較は、真にも偽にもなりません。そのため WHEN amount = NULL は、どの行にも当たりません。

amount >= 10000 などの条件にも当たらないので、金額が空の注文はELSEの「小口」に入ります。

私も、NULLの値が入っていて、計算が合わないかもと思ったことがあります。

確認方法：条件に使う列に、空の値が何件あるかを数えます。COUNT(*) - COUNT(amount) で、amountが空の件数が出ます。例の表は空の値がないので、0です。

どう直すか：IS NULL で判定し、1つ目のWHENに書きます。空の行の行き先がすぐ分かります。

WHEN amount IS NULL THEN '未入力'

それでも合わないとき：条件に使った列の値を一覧にする

4つを確かめても合わないときは、条件に使った列に、どんな値が入っているかを一覧にします。

列の値ごとに件数を出します。

SELECT channel, COUNT(*) FROM sales GROUP BY channel;

「ＥＣ」と「EC」が別の行に出たら、表記が違います。

全角と半角や余分な空白は、DBや設定によっては別の値として扱われます。channel = 'EC' に当たらない行は、ELSEの区分に入ります。

表記の違いは、channel IN ('EC', 'ＥＣ') のように両方を書くか、元のデータを1つの書き方にそろえます。

区分が正しくても、報告書の数字と合わないなら、集計の対象がずれていることがあります。確かめ方は、次の記事にまとめています。

業務への着地：区分の境目をSQLに残し、毎月同じ基準で数える

区分の条件をCASE WHENに書いておくと、境目がはっきりし、毎月同じ基準で数えられます。

区分を作るときに迷いやすいのは、境目の扱いです。1万円ちょうどを「以上」で大口に入れるのか、「未満」で中口に入れるのか。月ごとの集計なら、どの日付を基準に数えるのか。

私も区分を作るときは、以上・以下・未満や日付の基準を、よく人と確認します。

CASE WHENなら、amount >= 10000 のように、確認した基準がそのまま条件として残ります。1万円ちょうどは大口だと、誰が見ても分かります。

比べると、次のとおりです。

場面	Before	After
境目の扱い	担当者の解釈で決まる	確認した基準が条件に残る
翌月の集計	前月の基準を思い出す	同じSQLを実行し直す
報告書に写す前	区分の合計だけを見る	件数と最小・最大を確かめる

基準を人と確認してSQLに残すと、担当が変わっても、同じデータから同じ数字が出ます。