MySQL SUM 如果大于和小于

Question

如果它们的值超过 200，我需要从 price 列中求和。

否则，如果它们的值小于 200，我需要求和。

我想按日期分组的结果。

请查看订单表:

price       date_transaction
537         2014-03-28 11:44:40
123         2014-03-28 14:35:21
96          2014-03-28 15:43:17
341         2014-03-28 16:12:42
309         2014-03-29 15:47:48
223         2014-03-29 19:22:28
115         2014-03-29 23:31:34
109         2014-03-29 23:44:16

我想要这样的结果:

date_transaction    sum_of_two_hundred_or_more      sum_of_less_than_200
2014-03-28          878                             219
2014-03-29          532                             224
2014-03-30          0                               0
2014-03-31          0                               0

这是我试过的，但它只显示了 200 多个。

SELECT
DISTINCT DATE(date_transaction) AS date_transaction,
SUM(price) AS sum_of_two_hundred_or_more
FROM orders
WHERE price > 200
AND date_transaction BETWEEN '2014-03-26' AND '2014-03-31' + INTERVAL 1 DAY
GROUP BY DATE(date_transaction)
ORDER BY DATE(date_transaction) ASC;

Answer 1

这应该有效:

SELECT DATE(date_transaction) AS date_transaction,
    SUM (CASE WHEN price >= 200 THEN price ELSE 0 END) AS sum_of_two_hundred_or_more
    SUM (CASE WHEN price < 200 THEN price ELSE 0 END) AS sum_of_two_hundred_less
FROM orders
WHERE date_transaction BETWEEN '2014-03-26' AND '2014-03-31' + INTERVAL 1 DAY
GROUP BY DATE(date_transaction)
ORDER BY DATE(date_transaction) ASC