MySQL で SUM() と CASE() を組み合わせてデータを取得する
2016-06-01ちょっと凝ったSQLを作ろうとするときに使う SUM() とか CASE() ですが、いざ使おうとするときにちょっと書き方を忘れたりするので簡単な例で書き留めておきます。
目次
使うデータ
例として使うのは、売上の情報を持っている salesテーブルにあるデータです。
mysql> desc sales;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| item_name | varchar(100) | NO | | NULL | |
| item_quantity | int(11) | NO | | 0 | |
| total_price | int(11) | NO | | 0 | |
| purchase_date | date | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
mysql> SELECT * FROM sales;
+----+-----------+---------------+-------------+---------------+
| id | item_name | item_quantity | total_price | purchase_date |
+----+-----------+---------------+-------------+---------------+
| 1 | りんご | 1 | 100 | 2016-05-02 |
| 2 | みかん | 10 | 300 | 2016-05-03 |
| 3 | すいか | 2 | 600 | 2016-05-04 |
| 4 | りんご | 3 | 300 | 2016-05-06 |
| 5 | りんご | 4 | 400 | 2016-05-06 |
| 6 | みかん | 5 | 150 | 2016-05-06 |
| 7 | すいか | 2 | 600 | 2016-05-08 |
| 8 | いちご | 10 | 2000 | 2016-05-09 |
| 9 | みかん | 23 | 690 | 2016-05-10 |
| 10 | すいか | 5 | 1500 | 2016-05-11 |
+----+-----------+---------------+-------------+---------------+
SUM() を使って各商品の合計金額を出してみる
SUM() を使うときは、 GROUP BY 句もセットで使います。今回は各商品での合計を出すので、 item_name でグループ化します。
mysql> SELECT item_name, SUM(total_price) AS item_total_price FROM sales GROUP BY item_name;
+-----------+------------------+
| item_name | item_total_price |
+-----------+------------------+
| いちご | 2000 |
| すいか | 2700 |
| みかん | 1140 |
| りんご | 800 |
+-----------+------------------+
各日に売れた合計金額を出したい場合は次のようになります。
mysql> SELECT purchase_date, SUM(total_price) FROM sales GROUP BY purchase_date;
+---------------+------------------+
| purchase_date | SUM(total_price) |
+---------------+------------------+
| 2016-05-02 | 100 |
| 2016-05-03 | 300 |
| 2016-05-04 | 600 |
| 2016-05-06 | 850 |
| 2016-05-08 | 600 |
| 2016-05-09 | 2000 |
| 2016-05-10 | 690 |
| 2016-05-11 | 1500 |
+---------------+------------------+
SUM() と CASE() を組み合わせてみる
取得するときに条件をつけて取得できる CASE() ですが、**SUM()**と組み合わせるとデータを取得する段階で集計ができるので、取得後の処理が少なくなります。 今回の例では、2016年5月6日の前と後それぞれの売上合計金額を出したいと思います。まず取得結果から見てみます。
+-------------------------------+-------------------------------+
| 5月6日以前の合計金額 | 5月7日以降の合計金額 |
+-------------------------------+-------------------------------+
| 1850 | 4790 |
+-------------------------------+-------------------------------+
この結果が得られるSQLは以下のようになります。
SELECT
SUM(
CASE WHEN purchase_date <= "2016-05-06" THEN total_price ELSE 0 END
) AS 1,
SUM(
CASE WHEN purchase_date >= "2016-05-07" THEN total_price ELSE 0 END
) AS "5月7日以降の合計金額"
FROM sales;
CASE() の構文は
CASE WHEN A=B THEN C ELSE D END
という形で、A=Bなら C, それ以外の場合は D という意味です。
なので、上記の例では、 「purchase_date が “2016-05-06” 以前の場合は total_price, それ以外の場合は 0」 を**SUM()**の中に入れることで、purchase_date が “2016-05-06"以前のtotal_priceの合計 を出すことができます。
この方法を使えば、あるフラグを持つ場合のみ加算する といった使い方もできます。
comments powered by Disqus