Question 113328 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

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

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search