【SQL】CASE WHEN + GROUP BYで年齢層・金額帯・期間別に集計する方法｜構成比・ROLLUP・HAVING・CTE活用パターン完全解説

2024.05.08

「年齢層ごとの売上合計を出したい」「購入金額帯別に顧客数を数えたい」「月次・四半期別に件数と平均を比べたい」——こうした任意の区間でデータをグループ分けして集計する操作は、データ分析の現場で日常的に発生します。

SQLでこれを実現するのが CASE WHEN + GROUP BY の組み合わせです。本記事では基本パターンから、DBMS別のエイリアス問題・CTEを使ったクリーンな書き方・ROLLUPによる小計追加・ウィンドウ関数での構成比計算まで、実務で使えるパターンを網羅します。

この記事で学べること：CASE WHEN + GROUP BYの基本構文・GROUP BYでのエイリアスのDB別の扱い・CTEを使ったクリーンな集計クエリ・ROLLUPで小計・合計を自動追加・ウィンドウ関数でグループ内の構成比・累計を算出・HAVING で集計後に絞り込む・NULL値の扱い・実務バリエーション（金額帯・期間別・スコア帯）
スポンサーリンク

サンプルデータ

以降の解説では下記のテーブルを使います。

サンプルデータ作成
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    age         INT,              -- NULL あり（年齢未登録のケースを含む）
    prefecture  VARCHAR(20)
);

CREATE TABLE sales (
    sale_id     INT PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(10,2) NOT NULL,
    sale_date   DATE NOT NULL
);

INSERT INTO customers VALUES
    (1, '田中',   28, '東京'),
    (2, '佐藤',   35, '大阪'),
    (3, '鈴木',   42, '東京'),
    (4, '高橋',   31, '名古屋'),
    (5, '伊藤',   55, '大阪'),
    (6, '渡辺',   NULL, '東京'),  -- 年齢未登録
    (7, '山本',   23, '福岡');

INSERT INTO sales VALUES
    (101, 1, 12000, '2024-01-15'),
    (102, 2, 35000, '2024-01-20'),
    (103, 3, 8500,  '2024-02-10'),
    (104, 4, 22000, '2024-02-28'),
    (105, 5, 45000, '2024-03-05'),
    (106, 1, 9800,  '2024-03-12'),
    (107, 7, 15000, '2024-03-20');

基本パターン：CASE WHEN で年齢層グループを作る

CASE WHEN で連続値（年齢・金額・スコアなど）を任意の区間に分類し、GROUP BY でその区間ごとに集計します。

年齢層別の売上合計・平均・件数
SELECT
    CASE
        WHEN c.age BETWEEN 20 AND 29 THEN '20代'
        WHEN c.age BETWEEN 30 AND 39 THEN '30代'
        WHEN c.age BETWEEN 40 AND 49 THEN '40代'
        WHEN c.age >= 50             THEN '50代以上'
        WHEN c.age IS NULL           THEN '年齢未登録'
        ELSE                              '20歳未満'
    END                       AS age_group,
    COUNT(s.sale_id)          AS sales_count,
    SUM(s.amount)             AS total_amount,
    ROUND(AVG(s.amount), 0)   AS avg_amount,
    MAX(s.amount)             AS max_amount
FROM   customers c
JOIN   sales s ON c.customer_id = s.customer_id
GROUP BY
    CASE
        WHEN c.age BETWEEN 20 AND 29 THEN '20代'
        WHEN c.age BETWEEN 30 AND 39 THEN '30代'
        WHEN c.age BETWEEN 40 AND 49 THEN '40代'
        WHEN c.age >= 50             THEN '50代以上'
        WHEN c.age IS NULL           THEN '年齢未登録'
        ELSE                              '20歳未満'
    END
ORDER BY MIN(c.age);  -- 年齢順に並べる

実行結果イメージ：

age_group sales_count total_amount avg_amount max_amount
20代 3 36,800 12,267 15,000
30代 2 57,000 28,500 35,000
40代 1 8,500 8,500 8,500
50代以上 1 45,000 45,000 45,000
GROUP BY に CASE WHEN を繰り返す理由：SELECT句で age_group というエイリアスを定義しても、GROUP BY でそのエイリアスを参照できるかはDBMSによって異なります（後述）。確実に動かすには GROUP BY にも同じ CASE WHEN 式を繰り返すか、CTEを使います。

GROUP BY でエイリアスが使えるかどうか（DBMS別）

SELECT句のエイリアスをGROUP BYで参照できるかどうかはDBMSによって異なります。

DBMS GROUP BY でエイリアス使用 備考
MySQL ○ 使える MySQLはエイリアスをGROUP BYで参照できる独自拡張あり
PostgreSQL ○ 使える エイリアス参照をサポート
Oracle × 使えない SQL標準に従い、エイリアスはGROUP BY/WHERE不可
SQL Server × 使えない 同上。サブクエリまたはCTEで対応
MySQL / PostgreSQL: エイリアスが使える（短縮形）
-- MySQL / PostgreSQL はこの書き方が使える
SELECT
    CASE
        WHEN age BETWEEN 20 AND 29 THEN '20代'
        WHEN age BETWEEN 30 AND 39 THEN '30代'
        WHEN age BETWEEN 40 AND 49 THEN '40代'
        ELSE 'その他'
    END           AS age_group,
    SUM(amount)   AS total_amount,
    AVG(amount)   AS avg_amount
FROM customers
JOIN sales USING (customer_id)
GROUP BY age_group   -- ← エイリアスをそのまま使える
ORDER BY age_group;
Oracle / SQL Server: CTEで対応（全DB共通で最も安全）
-- CTEを使えばOracle/SQL Serverでも、かつ可読性も高い
WITH age_grouped AS (
    SELECT
        s.amount,
        CASE
            WHEN c.age BETWEEN 20 AND 29 THEN '20代'
            WHEN c.age BETWEEN 30 AND 39 THEN '30代'
            WHEN c.age BETWEEN 40 AND 49 THEN '40代'
            WHEN c.age >= 50             THEN '50代以上'
            WHEN c.age IS NULL           THEN '年齢未登録'
            ELSE                              'その他'
        END AS age_group
    FROM customers c
    JOIN sales s ON c.customer_id = s.customer_id
)
SELECT
    age_group,
    COUNT(*)                AS sales_count,
    SUM(amount)             AS total_amount,
    ROUND(AVG(amount), 0)   AS avg_amount
FROM   age_grouped
GROUP BY age_group
ORDER BY MIN(age_group);
CTEを推奨：CTEを使うと CASE WHEN を1回だけ書けばよく、GROUP BY でエイリアスを参照する問題も回避できます。Oracle・SQL Server でも動き、クエリの可読性も大幅に向上します。

グループの表示順を意図通りに制御する

「20代→30代→40代→50代以上」のように年齢順に表示したい場合、文字列エイリアスをそのままORDER BYにかけると辞書順（20代・30代・40代・50代以上は偶然正しい）になりますが、「10代」や「その他」を含む場合は明示的な順序指定が必要です。

グループの表示順を明示的に制御
-- 方法1: グループに数値の順序キーを持たせる
WITH age_grouped AS (
    SELECT
        amount,
        CASE
            WHEN age < 20              THEN 1
            WHEN age BETWEEN 20 AND 29 THEN 2
            WHEN age BETWEEN 30 AND 39 THEN 3
            WHEN age BETWEEN 40 AND 49 THEN 4
            WHEN age >= 50             THEN 5
            ELSE                            9  -- NULL/その他を末尾に
        END AS age_order,
        CASE
            WHEN age < 20              THEN '10代以下'
            WHEN age BETWEEN 20 AND 29 THEN '20代'
            WHEN age BETWEEN 30 AND 39 THEN '30代'
            WHEN age BETWEEN 40 AND 49 THEN '40代'
            WHEN age >= 50             THEN '50代以上'
            ELSE                            '年齢未登録'
        END AS age_group
    FROM customers c
    JOIN sales s ON c.customer_id = s.customer_id
)
SELECT
    age_group,
    COUNT(*)              AS sales_count,
    SUM(amount)           AS total_amount,
    ROUND(AVG(amount), 0) AS avg_amount
FROM   age_grouped
GROUP BY age_group, age_order
ORDER BY age_order;   -- 数値キーでソートすれば確実

-- 方法2: ORDER BY で CASE WHEN を直接使う
ORDER BY
    CASE age_group
        WHEN '20代'   THEN 1
        WHEN '30代'   THEN 2
        WHEN '40代'   THEN 3
        WHEN '50代以上' THEN 4
        ELSE 9
    END;

ROLLUPで小計・合計行を自動追加する

GROUP BY にROLLUPを付けると、各グループの集計行に加えて全体合計行が自動で追加されます。手動でUNION ALLで合計行を加える必要がなくなります。

GROUP BY ROLLUP（MySQL / PostgreSQL / Oracle）
-- MySQL / Oracle / PostgreSQL
SELECT
    COALESCE(
        CASE
            WHEN age BETWEEN 20 AND 29 THEN '20代'
            WHEN age BETWEEN 30 AND 39 THEN '30代'
            WHEN age BETWEEN 40 AND 49 THEN '40代'
            WHEN age >= 50             THEN '50代以上'
            ELSE                            'その他'
        END,
    '【合計】') AS age_group,   -- ROLLUPの合計行は NULL になるので COALESCE で置換
    COUNT(*)         AS sales_count,
    SUM(s.amount)    AS total_amount,
    ROUND(AVG(s.amount), 0) AS avg_amount
FROM   customers c
JOIN   sales s ON c.customer_id = s.customer_id
GROUP BY ROLLUP (
    CASE
        WHEN c.age BETWEEN 20 AND 29 THEN '20代'
        WHEN c.age BETWEEN 30 AND 39 THEN '30代'
        WHEN c.age BETWEEN 40 AND 49 THEN '40代'
        WHEN c.age >= 50             THEN '50代以上'
        ELSE                              'その他'
    END
);

-- SQL Server: 構文が同じ（GROUP BY ROLLUP (...)）
CTEとROLLUPを組み合わせるとクリーンに書ける
WITH age_grouped AS (
    SELECT
        s.amount,
        CASE
            WHEN c.age BETWEEN 20 AND 29 THEN '20代'
            WHEN c.age BETWEEN 30 AND 39 THEN '30代'
            WHEN c.age BETWEEN 40 AND 49 THEN '40代'
            WHEN c.age >= 50             THEN '50代以上'
            ELSE                              'その他'
        END AS age_group
    FROM customers c
    JOIN sales s ON c.customer_id = s.customer_id
)
SELECT
    COALESCE(age_group, '【合計】') AS age_group,
    COUNT(*)                        AS sales_count,
    SUM(amount)                     AS total_amount,
    ROUND(AVG(amount), 0)           AS avg_amount
FROM   age_grouped
GROUP BY ROLLUP (age_group)
ORDER BY GROUPING(age_group), age_group;
-- GROUPING() は合計行で1を返すため、合計行を末尾に表示できる

ウィンドウ関数でグループ内の構成比・累計を算出する

各グループの合計が全体に占める割合（構成比）や累計を計算したい場合は、ウィンドウ関数を使います。サブクエリで合計を計算するよりシンプルに書けます。

構成比（全体に対するグループの割合）
WITH age_grouped AS (
    SELECT
        s.amount,
        CASE
            WHEN c.age BETWEEN 20 AND 29 THEN '20代'
            WHEN c.age BETWEEN 30 AND 39 THEN '30代'
            WHEN c.age BETWEEN 40 AND 49 THEN '40代'
            WHEN c.age >= 50             THEN '50代以上'
            ELSE                              'その他'
        END AS age_group
    FROM customers c
    JOIN sales s ON c.customer_id = s.customer_id
),
group_totals AS (
    SELECT
        age_group,
        COUNT(*)              AS sales_count,
        SUM(amount)           AS group_total
    FROM   age_grouped
    GROUP BY age_group
)
SELECT
    age_group,
    sales_count,
    group_total,
    -- 全体合計（ウィンドウ関数でパーティションなし = 全行対象）
    SUM(group_total) OVER ()                          AS grand_total,
    -- 構成比（小数点1桁）
    ROUND(100.0 * group_total / SUM(group_total) OVER (), 1) AS pct,
    -- 累計（年齢順に並べた場合の累積売上）
    SUM(group_total) OVER (ORDER BY age_group ROWS UNBOUNDED PRECEDING) AS running_total
FROM   group_totals
ORDER BY age_group;

実行結果イメージ：

age_group sales_count group_total grand_total pct running_total
20代 3 36,800 147,300 25.0% 36,800
30代 2 57,000 147,300 38.7% 93,800
40代 1 8,500 147,300 5.8% 102,300
50代以上 1 45,000 147,300 30.6% 147,300

HAVINGで集計後のグループを絞り込む

「売上合計が1万円以上のグループだけ表示したい」など、集計結果に条件をかけるにはHAVINGを使います。WHERE句は集計前の絞り込み、HAVINGは集計後の絞り込みです。

HAVING で最小件数・最小売上を持つグループのみ表示
WITH age_grouped AS (
    SELECT
        s.amount,
        CASE
            WHEN c.age BETWEEN 20 AND 29 THEN '20代'
            WHEN c.age BETWEEN 30 AND 39 THEN '30代'
            WHEN c.age BETWEEN 40 AND 49 THEN '40代'
            WHEN c.age >= 50             THEN '50代以上'
            ELSE                              'その他'
        END AS age_group
    FROM customers c
    JOIN sales s ON c.customer_id = s.customer_id
)
SELECT
    age_group,
    COUNT(*)              AS sales_count,
    SUM(amount)           AS total_amount,
    ROUND(AVG(amount), 0) AS avg_amount
FROM   age_grouped
GROUP BY age_group
HAVING COUNT(*) >= 2          -- 購入件数2件以上のグループのみ
   AND SUM(amount) >= 20000   -- かつ合計2万円以上
ORDER BY total_amount DESC;
WHERE と HAVING の使い分け：
WHERE: グループ化・集計の前にフィルタ（個々の行に条件）
HAVING: グループ化・集計の後にフィルタ（集計結果に条件）
例: 「2024年の売上のみ対象 (WHERE) 」かつ「合計10万円以上のグループのみ (HAVING)」

NULLの扱い：ELSE句で必ず受け止める

年齢がNULLの顧客はどのCASE条件にも当てはまらず、ELSE句がなければNULLとして分類されます。NULLのグループはGROUP BYでは1つのグループとして扱われますが、意図が不明確になります。必ずELSE句で「年齢未登録」などのラベルを付けてください。

ELSE句なし vs ELSE句あり の比較
-- NG: ELSE なし → age IS NULLの顧客がNULLグループに
SELECT
    CASE
        WHEN age BETWEEN 20 AND 29 THEN '20代'
        WHEN age BETWEEN 30 AND 39 THEN '30代'
        -- ELSE なし: NULL の行は NULL グループに入る
    END AS age_group,
    COUNT(*) AS cnt
FROM customers
GROUP BY age_group;
-- NULL グループが表示されるが意味が不明確

-- OK: ELSE で明示的にラベル付け
SELECT
    CASE
        WHEN age BETWEEN 20 AND 29 THEN '20代'
        WHEN age BETWEEN 30 AND 39 THEN '30代'
        WHEN age IS NULL           THEN '年齢未登録'
        ELSE                            'その他'
    END AS age_group,
    COUNT(*) AS cnt
FROM customers
GROUP BY age_group;

実務バリエーション：年齢以外のグループ化パターン

購入金額帯別の顧客数と平均単価

金額帯別集計
WITH amount_grouped AS (
    SELECT
        customer_id,
        SUM(amount) AS total_purchase,
        CASE
            WHEN SUM(amount) <  10000             THEN '1万円未満'
            WHEN SUM(amount) BETWEEN 10000 AND 29999 THEN '1〜3万円'
            WHEN SUM(amount) BETWEEN 30000 AND 49999 THEN '3〜5万円'
            WHEN SUM(amount) >= 50000             THEN '5万円以上'
        END AS purchase_tier
    FROM   sales
    GROUP BY customer_id
)
SELECT
    purchase_tier,
    COUNT(*)                          AS customer_count,
    ROUND(AVG(total_purchase), 0)     AS avg_purchase,
    SUM(total_purchase)               AS tier_total
FROM   amount_grouped
GROUP BY purchase_tier
ORDER BY MIN(total_purchase);

期間（四半期）別の売上集計

四半期別集計（全DB共通）
SELECT
    CASE
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 1  AND 3  THEN 'Q1'
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 4  AND 6  THEN 'Q2'
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 7  AND 9  THEN 'Q3'
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 10 AND 12 THEN 'Q4'
    END                               AS quarter,
    EXTRACT(YEAR FROM sale_date)      AS fiscal_year,
    COUNT(*)                          AS sales_count,
    SUM(amount)                       AS total_amount,
    ROUND(AVG(amount), 0)             AS avg_amount
FROM   sales
GROUP BY
    EXTRACT(YEAR FROM sale_date),
    CASE
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 1  AND 3  THEN 'Q1'
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 4  AND 6  THEN 'Q2'
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 7  AND 9  THEN 'Q3'
        WHEN EXTRACT(MONTH FROM sale_date) BETWEEN 10 AND 12 THEN 'Q4'
    END
ORDER BY fiscal_year, quarter;

スコア帯別（会員ランク・評価スコアなど）

スコア帯別集計（会員ランク分析）
WITH score_grouped AS (
    SELECT
        customer_id,
        loyalty_score,
        CASE
            WHEN loyalty_score >= 90 THEN 'プラチナ'
            WHEN loyalty_score >= 70 THEN 'ゴールド'
            WHEN loyalty_score >= 50 THEN 'シルバー'
            ELSE                          'ブロンズ'
        END AS member_rank
    FROM customers
)
SELECT
    sg.member_rank,
    COUNT(DISTINCT sg.customer_id)  AS member_count,
    SUM(s.amount)                   AS total_sales,
    ROUND(AVG(s.amount), 0)         AS avg_sale_per_tx,
    ROUND(SUM(s.amount) / COUNT(DISTINCT sg.customer_id), 0) AS avg_sales_per_member
FROM   score_grouped sg
JOIN   sales s ON sg.customer_id = s.customer_id
GROUP BY sg.member_rank
ORDER BY MIN(sg.loyalty_score) DESC;

都道府県（地域）別集計

地域グループ別集計
WITH region_grouped AS (
    SELECT
        c.prefecture,
        s.amount,
        CASE c.prefecture
            WHEN '東京' THEN '関東'
            WHEN '神奈川' THEN '関東'
            WHEN '埼玉' THEN '関東'
            WHEN '千葉' THEN '関東'
            WHEN '大阪' THEN '関西'
            WHEN '京都' THEN '関西'
            WHEN '兵庫' THEN '関西'
            ELSE '其他地域'
        END AS region
    FROM   customers c
    JOIN   sales s ON c.customer_id = s.customer_id
)
SELECT
    region,
    COUNT(*)              AS sales_count,
    SUM(amount)           AS total_amount,
    ROUND(AVG(amount), 0) AS avg_amount
FROM   region_grouped
GROUP BY region
ORDER BY total_amount DESC;

よくある質問

QGROUP BYにCASE WHENのエイリアスを書いたらエラーになります
AOracle・SQL Serverでは SELECT 句のエイリアスをGROUP BYで参照できません。CTEでCASE WHENを先に計算してから GROUP BY age_group と書くのが最も安全です。MySQL・PostgreSQLではエイリアス参照が可能です。
Qグループを年齢順（20代→30代→…）に並べるには？
AORDER BY に CASE WHEN を使って数値キーで並べる方法が確実です。例: ORDER BY CASE age_group WHEN '20代' THEN 1 WHEN '30代' THEN 2 ... END。または CTE でソートキー列（age_order）を数値で持たせ、ORDER BY age_order とします。
Q合計行（全体計）も同時に表示するには？
AGROUP BY ROLLUP (...)を使うと自動で全体合計行が追加されます。ROLLUPの合計行はグループ列がNULLになるため、COALESCE(age_group, '【合計】')でラベルを付けてください。ROLLUP非対応の環境ではUNION ALL SELECT '合計', COUNT(*), SUM(amount), AVG(amount) FROM ...で手動追加します。
Qグループの構成比（%）も一緒に出すには？
Aウィンドウ関数 SUM(group_total) OVER () で全体合計を計算し、ROUND(100.0 * group_total / SUM(group_total) OVER (), 1) で構成比を算出します。本記事の「ウィンドウ関数で構成比を算出する」セクションのクエリをそのまま使えます。
QNULLの行をグループから除外するには？
A最も確実な方法は WHERE 句で除外することです。例: WHERE age IS NOT NULL。あるいは CASE の ELSE 句で「年齢未登録」ラベルを付けて含めた上で、HAVING で除外もできます: HAVING age_group != '年齢未登録'（MySQL・PostgreSQL）。

まとめ

CASE WHEN + GROUP BY の組み合わせは、連続値を任意のグループに分類して集計するデータ分析の基本テクニックです。

  • 基本形: CASE WHEN で区間を定義 → GROUP BY + SUM/AVG/COUNT で集計
  • ELSE句必須: NULLや想定外の値を必ずELSEで受け止め、意図しないNULLグループを防ぐ
  • CTE推奨: CASE WHEN を1か所に書き、GROUP BY はエイリアスで参照。全DB対応で可読性も高い
  • GROUP BY ROLLUP: 合計行を自動追加。COALESCEでNULLラベルを「合計」に変換
  • ウィンドウ関数: SUM OVER () で全体合計を取得し、構成比・累計を1クエリで算出
  • HAVING: 集計後の絞り込みに使用。「合計N件以上・N円以上のグループのみ」表示に便利
汎用パターン：年齢層だけでなく、購入金額帯・四半期・会員ランク・地域など、あらゆる連続値・カテゴリ変数に同じパターンが適用できます。CTEとROLLUPを組み合わせた構成を定型として覚えておくと、分析クエリが格段に書きやすくなります。

関連記事：CASE WHEN完全ガイド、GROUP BY + SUMで集計する方法、AVG関数で平均を求める方法、SUM関数完全ガイド