为什么我的各种CASE WHEN函数返回相同的值

Question

我正试图编写一个查询，该查询返回的计数取决于反馈字段的值，该值的范围为0-5(0表示未分级(。

我想要：

• 所有行的计数(任何级别为1或更高的行(
• 评级为1的所有行的计数(任意值=1(
• 所有评级为1的行也是给定任务的第一次迭代(任何评级为1且迭代为0的行(

我已经写了这个查询，但所有计数的值都相同：

select
DATE_FORMAT(created_at,'%M') as Month,
COUNT(CASE WHEN rate > 0 THEN 1 ELSE 0 END) AS total,
COUNT(CASE WHEN rate = 1 THEN 1 ELSE 0 END) AS Rated_1,
COUNT(CASE WHEN client_feedback = 1 AND index = 0 THEN 1 ELSE 0 END) AS first_iteration_rated_1
from tablexxx
where created_at between date('2022-04-01') and date('2022-10-01')
GROUP BY Month

Answer 1

尝试使用SUM()而不是COUNT()。

不管值是0还是1，Count()都将向上计数。

Answer 2

您可以有两种方法：

方法1：在CASE 的其他部分使用NULL

select
DATE_FORMAT(created_at,'%M') as Month,
COUNT(CASE WHEN rate > 0 THEN 1 ELSE null END) AS total,
COUNT(CASE WHEN rate = 1 THEN 1 ELSE null END) AS Rated_1,
COUNT(CASE WHEN client_feedback = 1 AND index = 0 THEN 1 ELSE null END) AS first_iteration_rated_1
from tablexxx
where created_at between date('2022-04-01') and date('2022-10-01')
GROUP BY Month

方法2：使用总和而不是计数

select
DATE_FORMAT(created_at,'%M') as Month,
SUM(CASE WHEN rate > 0 THEN 1 ELSE 0 END) AS total,
SUM(CASE WHEN rate = 1 THEN 1 ELSE 0 END) AS Rated_1,
SUM(CASE WHEN client_feedback = 1 AND index = 0 THEN 1 ELSE 0 END) AS first_iteration_rated_1
from tablexxx
where created_at between date('2022-04-01') and date('2022-10-01')
GROUP BY Month