SQLでCOUNT と CASE 式を使って条件に応じたデータのカウント方法

条件に一致した行だけを数える基本形はCOUNT(CASE WHEN 条件 THEN 1 END)です。ELSE 0を付けると不一致行も数えてしまいます。また、購入履歴などをJOINした後の行数と、重複を除いた顧客数は別物です。本記事ではこの2つの落とし穴も扱います。

SQLのCOUNT関数とCASE式を組み合わせることで、特定の条件に基づいたデータのカウントを効率的に行うことができます。これにより、単純なカウント以上の柔軟な集計が可能になります。本記事では、COUNT関数とCASE式の基本を理解し、具体的なSQLクエリ例を通じて実際の使用方法を学びます。

COUNT関数の基本

COUNT関数は、SQLでデータベース内の行数をカウントするために使用されます。基本的な使い方として、全行数や特定の列に値が存在する行数を取得することができます。

全行数をカウントする

テーブル内のすべての行をカウントするには、次のようにCOUNT(*)を使用します。

SELECT COUNT(*) FROM テーブル名;

特定の列の値をカウントする

特定の列に値が存在する行をカウントするには、COUNT(列名)を使用します。NULL値はカウントされません。

SELECT COUNT(列名) FROM テーブル名;

CASE式の基本

CASE式は、SQLで条件に応じた異なる値を返すために使用される条件式です。IF-THEN-ELSE文に似た構文で、特定の条件が満たされた場合に特定の値を返し、そうでない場合に別の値を返すことができます。

CASE式の構文

CASE式の基本構文は次の通りです。

CASE
    WHEN 条件1 THEN 結果1
    WHEN 条件2 THEN 結果2
    ...
    ELSE 結果N
END

各条件が順に評価され、最初に真となった条件の結果が返されます。真になる条件がない場合、ELSE部分の結果が返されます。ELSEが省略された場合、真になる条件がなければNULLが返されます。

簡単な例

たとえば、従業員の給与を評価して、特定の範囲に応じて評価を与える場合、次のようにCASE式を使用します。

SELECT 名前,
       CASE
           WHEN 給与 IS NULL THEN '不明'
           WHEN 給与 >= 100000 THEN '高'
           WHEN 給与 >= 50000 THEN '中'
           ELSE '低'
       END AS 給与評価
FROM 従業員;

この例では、給与が100000以上の場合に「高」、50000以上の場合に「中」、給与がNULLなら「不明」、その他の値は「低」という評価を返します。

COUNTとCASE式の組み合わせ方

COUNT関数とCASE式を組み合わせることで、特定の条件に基づいたデータのカウントが可能になります。これにより、単一のクエリで複数の条件を一度に評価して集計することができます。

条件に応じたカウントの基本構文

COUNT関数とCASE式を組み合わせる基本構文は次の通りです。

SELECT
    COUNT(CASE WHEN 条件1 THEN 1 END) AS 条件1のカウント,
    COUNT(CASE WHEN 条件2 THEN 1 END) AS 条件2のカウント
FROM テーブル名;

この構文では、各条件に応じた行数を個別にカウントします。CASE式が真の場合に1を返し、それをCOUNT関数でカウントします。

具体例

たとえば、顧客テーブルから男性と女性の顧客数をそれぞれカウントする場合、次のようにします。

SELECT
    COUNT(CASE WHEN 性別 = '男性' THEN 1 END) AS 男性のカウント,
    COUNT(CASE WHEN 性別 = '女性' THEN 1 END) AS 女性のカウント
FROM 顧客;

この例では、性別が「男性」の行と「女性」の行をそれぞれカウントします。

複数条件のカウント

さらに複数の条件を組み合わせることで、より詳細なカウントが可能です。たとえば、製品のステータスが「販売中」と「販売終了」の数をカウントする場合は次のようにします。

SELECT
    COUNT(CASE WHEN ステータス = '販売中' THEN 1 END) AS 販売中の数,
    COUNT(CASE WHEN ステータス = '販売終了' THEN 1 END) AS 販売終了の数
FROM 製品;

このようにして、特定の条件に基づくデータの集計が簡単に行えます。

実際のSQLクエリ例

ここでは、COUNT関数とCASE式を組み合わせて特定の条件に応じたデータをカウントする具体的なSQLクエリの例を紹介します。これにより、実際のデータベース操作でどのように活用できるかを理解できます。

例1: 顧客の年齢層ごとのカウント

顧客テーブルから年齢層ごとに顧客数をカウントするクエリです。

SELECT
    COUNT(CASE WHEN 年齢 < 20 THEN 1 END) AS under_20,
    COUNT(CASE WHEN 年齢 BETWEEN 20 AND 29 THEN 1 END) AS age_20s,
    COUNT(CASE WHEN 年齢 BETWEEN 30 AND 39 THEN 1 END) AS age_30s,
    COUNT(CASE WHEN 年齢 BETWEEN 40 AND 49 THEN 1 END) AS age_40s,
    COUNT(CASE WHEN 年齢 >= 50 THEN 1 END) AS age_50_plus
FROM 顧客;

このクエリでは、顧客の年齢に応じて5つの年齢層に分類し、それぞれの顧客数をカウントしています。

例2: 製品ステータスごとのカウント

製品テーブルから、各製品のステータスごとに数をカウントするクエリです。

SELECT
    COUNT(CASE WHEN ステータス = '在庫あり' THEN 1 END) AS in_stock,
    COUNT(CASE WHEN ステータス = '在庫切れ' THEN 1 END) AS out_of_stock,
    COUNT(CASE WHEN ステータス = '販売終了' THEN 1 END) AS discontinued
FROM 製品;

このクエリでは、製品のステータスが「在庫あり」、「在庫切れ」、「販売終了」のそれぞれの場合の数をカウントしています。

例3: 注文ステータスごとのカウント

注文テーブルから、注文のステータスごとに数をカウントするクエリです。

SELECT
    COUNT(CASE WHEN ステータス = '新規' THEN 1 END) AS new_orders,
    COUNT(CASE WHEN ステータス = '処理中' THEN 1 END) AS processing_orders,
    COUNT(CASE WHEN ステータス = '完了' THEN 1 END) AS completed_orders
FROM 注文;

このクエリでは、注文のステータスが「新規」、「処理中」、「完了」のそれぞれの場合の数をカウントしています。

応用例

COUNT関数とCASE式の組み合わせは、さらに複雑な条件でのデータ集計にも応用できます。ここでは、実務で役立ついくつかの応用例を紹介します。

例1: 顧客の購入状況に基づくカウント

顧客テーブルと購入履歴テーブルを使用して、顧客が過去1年に購入したかどうかに基づいて顧客数をカウントします。

-- SQL Server用。基準時刻を固定し、未来の購入日は含めない
DECLARE @as_of datetime2 = SYSDATETIME();

WITH customer_flags AS (
    SELECT c.顧客ID,
           MAX(CASE WHEN p.購入日 >= DATEADD(YEAR, -1, @as_of)
                     AND p.購入日 < @as_of THEN 1 ELSE 0 END) AS has_recent
    FROM 顧客 AS c
    LEFT JOIN 購入履歴 AS p ON p.顧客ID = c.顧客ID
    GROUP BY c.顧客ID
)
SELECT COUNT(CASE WHEN has_recent = 1 THEN 1 END) AS recent_customers,
       COUNT(CASE WHEN has_recent = 0 THEN 1 END) AS non_recent_customers
FROM customer_flags;

この例はSQL Server用です。顧客IDが顧客テーブルの一意キーである前提で、先に顧客1人を1行へまとめます。最近の購入が2件ある人も1人となり、古い購入と最近の購入を両方持つ人が両分類へ入ることもありません。範囲は1年前以上・基準時刻未満です。MySQLやPostgreSQLでは日付関数と変数の書き方を変更してください。

例2: 商品カテゴリごとの売上カウント

商品カテゴリごとの売上数をカウントするクエリです。売上テーブルと商品テーブルを結合して集計します。

SELECT
    商品カテゴリ,
    COUNT(CASE WHEN 売上金額 > 0 THEN 1 END) AS positive_sales_rows
FROM 売上
JOIN 商品 ON 売上.商品ID = 商品.商品ID
GROUP BY 商品カテゴリ;

このクエリが数えるのは、商品IDが商品テーブルで一意という前提で、売上金額が正の売上行数です。販売数量の合計や、重複しない商品数ではありません。後者ならCOUNT(DISTINCT CASE WHEN 売上金額 > 0 THEN 売上.商品ID END)など、目的に合う集計を選びます。

例3: 複数条件でのカウント

複数の条件を組み合わせてデータをカウントするクエリです。例えば、顧客の年齢層ごとの購入状況をカウントします。

SELECT
    COUNT(DISTINCT CASE WHEN 年齢 < 20 AND 購入日 IS NOT NULL THEN 顧客.顧客ID END) AS buyers_under_20,
    COUNT(DISTINCT CASE WHEN 年齢 BETWEEN 20 AND 29 AND 購入日 IS NOT NULL THEN 顧客.顧客ID END) AS buyers_20s,
    COUNT(DISTINCT CASE WHEN 年齢 BETWEEN 30 AND 39 AND 購入日 IS NOT NULL THEN 顧客.顧客ID END) AS buyers_30s,
    COUNT(DISTINCT CASE WHEN 年齢 BETWEEN 40 AND 49 AND 購入日 IS NOT NULL THEN 顧客.顧客ID END) AS buyers_40s,
    COUNT(DISTINCT CASE WHEN 年齢 >= 50 AND 購入日 IS NOT NULL THEN 顧客.顧客ID END) AS buyers_50_plus
FROM 顧客 LEFT JOIN 購入履歴 ON 顧客.顧客ID = 購入履歴.顧客ID;

このクエリでは、購入日がNULLでない履歴を持つ顧客IDを重複排除して数えます。1人に複数の購入履歴があっても同じ年齢層で1人です。年齢NULLはどの年齢層にも入らないため、必要なら不明区分を追加してください。

ELSE 0・NULL・0件の違いを確認する

COUNTは0も非NULLの値として数えます。条件が真でない行を除くにはELSEを省略するかELSE NULLとします。SUMなら1と0の合計で件数を表せますが、入力自体が0行の集約ではNULLとなるためCOALESCEが必要です。GROUP BYで該当グループが存在しない場合は、0の行ではなく結果行自体がありません。

SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count,
    COALESCE(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 0) AS paid_sum,
    COUNT(CASE WHEN status IS NULL THEN 1 END) AS unknown_count
FROM orders;

WHEREで先にstatus = ‘paid’だけへ絞ると、未払い件数など他の集計の母集団も消えます。また独立したCASE列で条件が重なる場合、1行が複数列へ数えられます。分類の合計を総数と一致させたいなら、互いに重ならない条件とNULL・その他の扱いを決めてください。

少量データで期待値を確認する

たとえばstatusがpaid、pending、NULL、paidの4行なら、正しいpaid_countは2、unknown_countは1です。COUNT(CASE WHEN status = ‘paid’ THEN 1 ELSE 0 END)は4になり、誤りを発見できます。JOIN例は「最近2件」「古い購入と最近の購入の両方」「購入なし」「NULL日付」「未来日付」を含むテストデータで確認すると、集計単位の間違いを見つけやすくなります。

COUNT/CASEの基本形は多くのDBで使えますが、日付演算・識別子の引用符・戻り値型はDBごとに確認します。SQL Serverの大規模集計ではCOUNT_BIGも検討してください。SQLの書き方だけで高速化を保証せず、対象件数、結合キー、インデックス、実行計画を確認します。

参考：Microsoft Learn：COUNT、CASE、PostgreSQL：集約関数。

まとめ

COUNT関数とCASE式を組み合わせることで、SQLで特定の条件に基づいたデータのカウントが効率的に行えます。この方法は、単純な集計だけでなく、複数の条件を一度に評価してデータを詳細に分析する際にも非常に有用です。具体的なクエリ例を通じて、年齢層ごとの顧客数や製品ステータスごとのカウントなど、実際のデータベース操作でどのように活用できるかを学びました。これらのテクニックを活用することで、より高度なデータ分析とレポート作成が可能になります。