Search code examples
mysqlsqlcase

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

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
Solution

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

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