Why are my various CASE WHEN functions returning the same values?-CodePudding

Im trying to write a query that returns a count depending on the value of a feedback field that ranges from 0-5 (0 meaning that it was not rated).

I want:

Count of all rows ( anything rated 1 or greater)
Count of all rows rated as 1 (anything = 1)
And all rows rated as 1 and also is the first iteration of a given task (anything rated =1 and iteration = 0)

I have written this query but I am getting the same value for all counts:

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

CodePudding user response：

you can have two approaches:

method 1: use NULL in else part of the CASE

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

method 2: use sum instead of count

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

CodePudding user response：

Try to use SUM() instead of COUNT().

Count() will count up regardless of the value being 0 or 1.