Help us understand the problem. What is going on with this article?

SUM(CASE WHEN)を使った条件付き集計

More than 5 years have passed since last update.

MySQLのSUM関数で、集計条件を指定できることがわかったのでメモ。

SELECT SUM(CASE WHEN flag = 1 THEN 1 ELSE 0 END) FROM table

売上予定テーブルを作って、プロジェクトごとの売上額と、当月時点の売上残を集計してみる。

CREATE TABLE `売上予定` (
  `id`                int           NOT NULL AUTO_INCREMENT,
  `プロジェクトID`      varchar(3)    NOT NULL,
  `計上年月日`         date           NOT NULL,
  `金額`              decimal(11,2)  NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

データを用意します。

  • プロジェクト001は、1月〜6月まで毎月100,000円を売上予定
  • プロジェクト002は、3月〜4月まで毎月50,000円を売上予定
INSERT INTO `売上予定` VALUES (1, '001', '2015-01-31', 100000);
INSERT INTO `売上予定` VALUES (2, '001', '2015-02-28', 100000);
INSERT INTO `売上予定` VALUES (3, '001', '2015-03-31', 100000);
INSERT INTO `売上予定` VALUES (4, '001', '2015-04-30', 100000);
INSERT INTO `売上予定` VALUES (5, '001', '2015-05-31', 100000);
INSERT INTO `売上予定` VALUES (6, '001', '2015-06-30', 100000);
INSERT INTO `売上予定` VALUES (7, '002', '2015-03-31', 50000);
INSERT INTO `売上予定` VALUES (8, '002', '2015-04-30', 50000);

プロジェクトごとの売上額と、4月末の売上残を集計してみます。

売上額は単純にSUM(金額)で集計します。

売上残は計上月が4月以降の金額を合計したいので、SUM(case when)を使って集計条件を指定しています。

SELECT
  `プロジェクトID`,
  SUM(`金額`) AS `売上額`,
  SUM(CASE WHEN `計上年月日` > '2015-04-30' THEN `金額` ELSE 0 END) AS `売上残`
FROM `売上予定`
GROUP BY `プロジェクトID`;

SQLを実行すると、次のような結果になります。

プロジェクトID    売上額       売上残
001             600000.00   200000.00
002             100000.00   0.00
hisaitami
PHPer, running through the dark
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした