【SQL】GROUP BYで件数をカウント＆条件付きで絞り込む方法｜HAVING・条件付きCOUNT・複合グループ化・パフォーマンス完全ガイド

SELECT
    グループ化カラム,
    COUNT(*)        AS 件数,
    COUNT(カラム名) AS NULL除外件数
FROM テーブル名
WHERE 行フィルター条件     -- ③GROUP BY の前に適用
GROUP BY グループ化カラム
HAVING COUNT(*) >= 閾値  -- ⑤集計後に適用
ORDER BY 件数 DESC;

SELECT
    category,
    COUNT(*) AS product_count
FROM products
WHERE is_active = 1            -- 販売中の商品のみ集計
GROUP BY category
HAVING COUNT(*) >= 5           -- 5件以上のカテゴリだけ表示
ORDER BY product_count DESC;

-- NG: WHERE に集計関数は書けない
SELECT user_id, COUNT(*) FROM orders
WHERE COUNT(*) >= 3            -- エラー: WHEREに集計関数は不可
GROUP BY user_id;

-- OK: 集計前の条件は WHERE, 集計後の条件は HAVING
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'confirmed'     -- 確定済み注文だけで集計
GROUP BY user_id
HAVING COUNT(*) >= 3;          -- 3件以上のユーザーのみ表示

-- orders テーブル（shipped_at が NULL = 未発送）
-- | user_id | shipped_at |
-- | 1       | 2024-01-10 |
-- | 1       | NULL       |
-- | 2       | 2024-01-12 |

SELECT
    user_id,
    COUNT(*)           AS total_orders,    -- NULL含む全件: 1→2, 2→1
    COUNT(shipped_at)  AS shipped_orders   -- NULL除外: 1→1, 2→1
FROM orders
GROUP BY user_id;

-- 注文ステータス別の件数を1クエリで取得
SELECT
    user_id,
    COUNT(*)                                          AS total,
    COUNT(CASE WHEN status = 'confirmed' THEN 1 END) AS confirmed,
    COUNT(CASE WHEN status = 'shipped'   THEN 1 END) AS shipped,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY user_id
ORDER BY total DESC;

-- COUNT(CASE WHEN ... THEN 1 END) は NULL を返した行を除外してカウント
-- SUM(CASE WHEN ... THEN 1 ELSE 0 END) も同じ結果になる
-- ただし ELSE 0 を書いた場合: SUM は 0 を加算するため必ず同じ

SELECT
    department,
    COUNT(CASE WHEN score >= 80 THEN 1 END)        AS high_score_count,
    SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END)   AS high_score_sum,
    -- 構成比も同時に計算
    ROUND(
        100.0 * COUNT(CASE WHEN score >= 80 THEN 1 END) / COUNT(*), 1
    )                                               AS high_score_rate
FROM employees
GROUP BY department;

-- 「キャンセル率が20%以上のユーザー」を抽出
SELECT
    user_id,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY user_id
HAVING
    COUNT(*) >= 3   -- 3件以上発注したことがある
    AND COUNT(CASE WHEN status = 'cancelled' THEN 1 END) * 100
        / COUNT(*) >= 20;  -- キャンセル率20%以上

-- 年月 × カテゴリ の件数集計
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS year_month,
    category,
    COUNT(*) AS order_count
FROM orders
JOIN products USING (product_id)
GROUP BY DATE_FORMAT(order_date, '%Y-%m'), category
ORDER BY year_month, category;

-- PostgreSQL: DATE_TRUNC でグループ化
SELECT
    DATE_TRUNC('month', order_date) AS year_month,
    category,
    COUNT(*) AS order_count
FROM orders
JOIN products USING (product_id)
GROUP BY DATE_TRUNC('month', order_date), category
ORDER BY year_month, category;

-- Oracle: TRUNC でグループ化
SELECT
    TRUNC(order_date, 'MM') AS year_month,
    category,
    COUNT(*) AS order_count
FROM orders
JOIN products USING (product_id)
GROUP BY TRUNC(order_date, 'MM'), category
ORDER BY year_month, category;

-- MySQL / PostgreSQL / Oracle / SQL Server 全対応
SELECT
    COALESCE(category, '【全カテゴリ合計】') AS category,
    COALESCE(status, '【ステータス小計】')   AS status,
    COUNT(*) AS cnt
FROM orders
JOIN products USING (product_id)
GROUP BY ROLLUP (category, status)
ORDER BY category, status;

-- MySQL 互換構文 (WITH ROLLUP) ← MySQL の全バージョンで動作
SELECT category, status, COUNT(*) AS cnt
FROM orders
JOIN products USING (product_id)
GROUP BY category, status WITH ROLLUP;

-- 顧客ごとの注文件数と合計金額
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id)  AS order_count,
    SUM(o.amount)      AS total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(o.order_id) >= 2
ORDER BY total_amount DESC;

-- 注文のない顧客も含め、顧客全員の注文件数を集計
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count  -- NULLは除外されるため0になる
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;

-- 注文0件の顧客だけ抽出
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(o.order_id) = 0;

-- 「注文件数3件以上 かつ 合計金額1万円以上」のユーザー
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3
   AND SUM(amount) >= 10000;

-- 「5件以上 または 合計5万円以上」の VIPユーザー候補
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5
    OR SUM(amount) >= 50000;

-- 全体平均件数より多い注文数を持つユーザー
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > (
    SELECT AVG(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM orders
        GROUP BY user_id
    ) t
);

-- CTE を使った読みやすい書き方（MySQL 8.0+ / PostgreSQL / Oracle / SS）
WITH user_counts AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
),
avg_count AS (
    SELECT AVG(order_count) AS avg_cnt
    FROM user_counts
)
SELECT uc.user_id, uc.order_count
FROM user_counts uc
CROSS JOIN avg_count ac
WHERE uc.order_count > ac.avg_cnt
ORDER BY uc.order_count DESC;

-- GROUP BY: ユーザー別の合計注文件数（行が集約される）
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- ウィンドウ関数: 各注文行にユーザーの合計注文件数を付与
-- （元の注文行を保持したまま集計値を追加）
SELECT
    order_id,
    user_id,
    amount,
    COUNT(*) OVER (PARTITION BY user_id) AS user_total_orders,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total_amount
FROM orders;

-- NG: ORDER_DATE にインデックスがあるのに全件GROUP BY後にWHERE
-- （HAVINGは集計後フィルターのためインデックスが使えない）
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS ym,
    COUNT(*) AS cnt
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
HAVING ym >= '2024-01';  -- NG: 全件集計してから絞り込む

-- OK: WHERE で先に絞り込んでからGROUP BY
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS ym,
    COUNT(*) AS cnt
FROM orders
WHERE order_date >= '2024-01-01'  -- インデックスが効く
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

EXPLAIN SELECT
    category,
    COUNT(*) AS cnt
FROM orders
JOIN products USING (product_id)
WHERE order_date >= '2024-01-01'
GROUP BY category
HAVING COUNT(*) >= 10;

-- Using temporary: 一時テーブルが作られている → 遅い
-- Using filesort: 追加ソートが走っている → 遅い
-- Using index: インデックスのみで処理 → 速い
-- カバリングインデックスを作ると Using temporary が消える
CREATE INDEX idx_orders_date_cat
    ON orders (order_date, product_id);

-- 重複しているメールアドレスを一覧表示
SELECT
    email,
    COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) >= 2
ORDER BY duplicate_count DESC;

-- 重複行の詳細も一緒に取得（サブクエリ版）
SELECT *
FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) >= 2
)
ORDER BY email, created_at;

WITH monthly_au AS (
    SELECT
        DATE_FORMAT(action_date, '%Y-%m')   AS ym,
        COUNT(DISTINCT user_id)               AS mau
    FROM user_actions
    WHERE action_date >= '2024-01-01'
    GROUP BY DATE_FORMAT(action_date, '%Y-%m')
)
SELECT
    ym,
    mau,
    mau - LAG(mau) OVER (ORDER BY ym) AS mau_diff
FROM monthly_au
ORDER BY ym;

WITH ranked AS (
    SELECT
        o.user_id,
        p.category,
        COUNT(*) AS order_count,
        RANK() OVER (
            PARTITION BY p.category
            ORDER BY COUNT(*) DESC
        ) AS rnk
    FROM orders o
    JOIN products p USING (product_id)
    GROUP BY o.user_id, p.category
)
SELECT user_id, category, order_count, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk;

-- MySQL
SELECT
    DAYNAME(order_date)                                    AS day_of_week,
    COUNT(CASE WHEN status = 'confirmed'  THEN 1 END)      AS confirmed,
    COUNT(CASE WHEN status = 'shipped'    THEN 1 END)      AS shipped,
    COUNT(CASE WHEN status = 'cancelled'  THEN 1 END)      AS cancelled,
    COUNT(*)                                               AS total
FROM orders
GROUP BY DAYNAME(order_date), DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);