mysql - MySQL SUM大于或小于

如果它们的值超过200，我需要从价格栏中求和。
否则，如果它们的值小于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;

最佳答案

这应该有效：

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

关于mysql - MySQL SUM大于或小于，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37154066/