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 | apple | 1 | 100 | 2016-05-02 |
| 2 | orange | 10 | 300 | 2016-05-03 |
| 3 | watermelon | 2 | 600 | 2016-05-04 |
| 4 | apple | 3 | 300 | 2016-05-06 |
| 5 | apple | 4 | 400 | 2016-05-06 |
| 6 | orange | 5 | 150 | 2016-05-06 |
| 7 | watermelon | 2 | 600 | 2016-05-08 |
| 8 | strawberry | 10 | 2000 | 2016-05-09 |
| 9 | orange | 23 | 690 | 2016-05-10 |
| 10 | watermelon | 5 | 1500 | 2016-05-11 |
+----+-------------+---------------+-------------+---------------+
SUM() を使って各商品の合計金額を出してみる
SUM() を使うときは、 GROUP BY 句もセットで使います。今回は各商品での合計を出すので、 item_name でグループ化します。
SELECT
item_name,
SUM(total_price) AS item_total_price
FROM sales
GROUP BY item_name;
+-------------+------------------+
| item_name | item_total_price |
+-------------+------------------+
| apple | 800 |
| orange | 1140 |
| water melon | 2700 |
| strawberry | 2000 |
+-------------+------------------+
各日に売れた合計金額を出したい場合は次のようになります。
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日の前と後それぞれの売上合計金額を出したいと思います。まず取得結果から見てみます。
+--------------------------------+-------------------------------+
| sum of total before 2016/05/06 | sum of total after 2016/05/07 |
+--------------------------------+-------------------------------+
| 1850 | 4790 |
+--------------------------------+-------------------------------+
この結果が得られるSQLは以下のようになります。
SELECT
SUM(
CASE WHEN purchase_date <= "2016-05-06" THEN total_price ELSE 0 END
) AS "sum of total before 2016/05/06",
SUM(
CASE WHEN purchase_date >= "2016-05-07" THEN total_price ELSE 0 END
) AS "sum of total after 2016/05/07"
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