【SQL】SUM関数完全ガイド｜GROUP BY・WHERE条件・SUM(CASE WHEN)・SUM OVER()・NULL対策・HAVING・JOINの重複対策まで

-- sales テーブルの amount 列をすべて合計
SELECT SUM(amount) AS total_amount
FROM sales;
-- 結果: 1,250,000（全売上の合計）

-- WHERE で絞り込んでから合計
SELECT SUM(amount) AS electronics_total
FROM sales
WHERE category = '家電';
-- 結果: 450,000（家電カテゴリだけの合計）

-- 複数の数値列をそれぞれ合計
SELECT
    SUM(quantity)      AS total_qty,
    SUM(unit_price)    AS total_price,
    SUM(discount)      AS total_discount,
    SUM(quantity * unit_price) AS total_revenue  -- 計算式も合計できる
FROM order_items;

-- カテゴリ別の売上合計
SELECT
    category,
    SUM(amount) AS total_amount,
    COUNT(*)    AS order_count
FROM sales
GROUP BY category
ORDER BY total_amount DESC;
-- 結果:
-- 家電    | 450,000 | 120
-- 食品    | 380,000 | 250
-- 衣料品  | 220,000 | 95

-- 年月×カテゴリ別の売上合計
SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,  -- MySQL
    category,
    SUM(amount) AS total_amount
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), category
ORDER BY sale_month, category;
-- PostgreSQL: TO_CHAR(sale_date, 'YYYY-MM')
-- SQL Server: FORMAT(sale_date, 'yyyy-MM')

-- データ: amount = 100, 200, NULL, 300
SELECT SUM(amount) FROM sales;
-- 結果: 600（NULLは無視される。100+200+300）

-- 0件の場合
SELECT SUM(amount) FROM sales WHERE 1 = 0;
-- 結果: NULL（0ではない！）

-- NULL を 0 として扱いたい場合
SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM sales WHERE 1 = 0;
-- 結果: 0

-- quantity=3, unit_price=NULL の行がある場合
SELECT SUM(quantity * unit_price) FROM order_items;
-- 3 * NULL = NULL → この行は合計に含まれない

-- NULL を 0 として計算したい場合
SELECT SUM(quantity * COALESCE(unit_price, 0)) FROM order_items;
-- 3 * 0 = 0 → この行は 0 として合計に含まれる

-- データ: amount = 100, 200, 100, 300
SELECT SUM(amount)          AS total;       -- 700（全て合計）
SELECT SUM(DISTINCT amount) AS unique_total; -- 600（100+200+300）

-- 実務では JOINで行が膨らんだとき に使うことが多い
SELECT
    c.customer_id,
    SUM(DISTINCT o.amount) AS unique_order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;

-- 先に注文テーブルで集計してからJOIN
SELECT
    c.customer_id,
    c.name,
    o_sum.total_amount
FROM customers c
JOIN (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) o_sum ON c.customer_id = o_sum.customer_id;

-- カテゴリ別の売上を横並びで表示
SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
    SUM(CASE WHEN category = '家電'   THEN amount ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = '食品'   THEN amount ELSE 0 END) AS food,
    SUM(CASE WHEN category = '衣料品' THEN amount ELSE 0 END) AS clothing,
    SUM(amount) AS total
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY sale_month;
-- 結果:
-- 2024-01 | 150,000 | 120,000 | 80,000 | 350,000
-- 2024-02 | 180,000 | 130,000 | 70,000 | 380,000

-- 注文ステータスごとの金額合計
SELECT
    customer_id,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_total,
    SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_total,
    SUM(CASE WHEN status = 'pending'   THEN amount ELSE 0 END) AS pending_total
FROM orders
GROUP BY customer_id;

-- 各行に全体合計と構成比を表示
SELECT
    category,
    amount,
    SUM(amount) OVER ()                      AS grand_total,
    ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct
FROM sales;
-- 結果:
-- 家電 | 15,000 | 1,250,000 | 1.2%
-- 食品 |  8,000 | 1,250,000 | 0.6%

-- カテゴリ別の合計を各行に付与（GROUP BY不要）
SELECT
    category,
    product_name,
    amount,
    SUM(amount) OVER (PARTITION BY category) AS category_total
FROM sales;
-- 結果:
-- 家電 | テレビ    | 50,000 | 450,000
-- 家電 | エアコン  | 80,000 | 450,000
-- 食品 | 米        |  3,000 | 380,000

-- 日付順に売上の累計を計算
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;
-- 結果:
-- 2024-01-01 | 10,000 |  10,000
-- 2024-01-02 | 15,000 |  25,000
-- 2024-01-03 |  8,000 |  33,000

-- カテゴリ別の累計
SELECT
    category,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY category
        ORDER BY sale_date
    ) AS category_running_total
FROM sales;

-- 直近3件の売上合計（移動合計）
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_sum_3
FROM sales;

-- 売上合計が100,000以上のカテゴリだけ抽出
SELECT
    category,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category
HAVING SUM(amount) >= 100000
ORDER BY total_amount DESC;

-- WHERE と HAVING の併用
SELECT
    category,
    SUM(amount) AS total_amount
FROM sales
WHERE sale_date >= '2024-01-01'    -- 集約前: 行単位のフィルタ
GROUP BY category
HAVING SUM(amount) >= 100000       -- 集約後: グループ単位のフィルタ
ORDER BY total_amount DESC;

-- 平均を超える売上のカテゴリを抽出
SELECT category, SUM(amount) AS total
FROM sales
GROUP BY category
HAVING SUM(amount) > (
    SELECT AVG(category_total) FROM (
        SELECT SUM(amount) AS category_total
        FROM sales
        GROUP BY category
    ) sub
);

-- 顧客の累計購入額を更新
UPDATE customers c
SET total_spent = (
    SELECT COALESCE(SUM(o.amount), 0)
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- INT列の合計がBIGINTの上限を超えるケース（極端に大量のデータ）
-- → CAST で事前に型を拡張する
SELECT SUM(CAST(amount AS BIGINT)) FROM huge_table;

-- 金額はDECIMAL型で保持するのが安全
-- FLOAT/DOUBLE は誤差が出る
SELECT SUM(price) FROM products;  -- price が FLOAT だと 999.999...98 のような誤差が出得る