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

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