php mysql中的一个列的条件计数

i want to count all buy and sell in x duration. i have tried the following query, but its not giving the correct result.

我想计算x持续时间内的所有买卖。我尝试了以下查询，但它没有给出正确的结果。

select pair, duration, mode,
       count(case when mode='BUY' then 1 else 0 end) as BUYS,
       count(case when mode='SELL' then 1 else 0 end) as SELLS
from trends
where pair like 'EURAUD '
GROUP BY DURATION, MODE
ORDER BY MODE, DURATION
 duration | mode|BUYS|SELLS
 -------------------------
 1Dy      | BUY | 1  | 1
 1M       | BUY | 18 | 18
 1Wk      | BUY | 1  | 1
 5M       | BUY | 3  | 3
 15M      | SELL| 2  | 2
 1Hr      | SELL| 1  | 1
 1M       | SELL| 25 | 25
 30M      | SELL| 1  | 1
 5M       | SELL| 4  | 4

its showing the above result, thats not correct. it show the following result

它显示上述结果，这是不正确的。它显示以下结果

 duration | mode|BUYS|SELLS
 -------------------------
 1Dy      | BUY | 1  | 0
 1M       | BUY | 18 | 0
 1Wk      | BUY | 1  | 0
 5M       | BUY | 3  | 0
 15M      | SELL| 0  | 4
 1Hr      | SELL| 0  | 1
 1M       | SELL| 0  | 25
 30M      | SELL| 0  | 1
 5M       | SELL| 0  | 4

1 个解决方案

#1

You want sum(), not count():

你想要sum（），而不是count（）：

select pair, duration, mode,
       sum(case when mode = 'BUY' then 1 else 0 end) as BUYS,
       sum(case when mode = 'SELL' then 1 else 0 end) as SELLS
from trends
where pair like 'EURAUD '
GROUP BY DURATION, MODE
ORDER BY MODE, DURATION;

COUNT() counts the number of non-NULL values. Your case expression never returns NULL, so the count always returns the same values. I prefer using SUM() anyways.

COUNT（）计算非NULL值的数量。您的case表达式永远不会返回NULL，因此count始终返回相同的值。我更喜欢使用SUM（）。

#1