MySQL COUNT IF with GROUP BY

Question

I'm trying to solve this LeetCode problem (https://leetcode.com/problems/get-highest-answer-rate-question/):

I have generated the survey_log locally:

mysql> select * from survey_log;
+------+--------+-------------+-----------+-------+-----------+
| uid  | action | question_id | answer_id | q_num | timestamp |
+------+--------+-------------+-----------+-------+-----------+
|    5 | show   |         285 |      NULL |     1 |       123 |
|    5 | answer |         285 |    124124 |     1 |       124 |
|    5 | show   |         369 |      NULL |     2 |       125 |
|    5 | skip   |         369 |      NULL |     2 |       126 |
+------+--------+-------------+-----------+-------+-----------+

I'd like to use this auxiliary table:

mysql> select question_id, if(action='show', 1, 0) as is_show, if(action='answer', 1, 0) as is_answer from survey_log;
+-------------+---------+-----------+
| question_id | is_show | is_answer |
+-------------+---------+-----------+
|         285 |       1 |         0 |
|         285 |       0 |         1 |
|         369 |       1 |         0 |
|         369 |       0 |         0 |
+-------------+---------+-----------+

Next, what I'd like to do is get the sum of the is_show and is_answer columns for each question_id. I thought that this would work:

mysql> select question_id, count(if(action = 'show', 1, 0)) as show_count, count(if(action = 'answer', 1, 0)) as answer_count from survey_log group by question_id;
+-------------+------------+--------------+
| question_id | show_count | answer_count |
+-------------+------------+--------------+
|         285 |          2 |            2 |
|         369 |          2 |            2 |
+-------------+------------+--------------+

However, this doesn't give me the expected output

+-------------+------------+--------------+
| question_id | show_count | answer_count |
+-------------+------------+--------------+
|         285 |          1 |            1 |
|         369 |          1 |            0 |
+-------------+------------+--------------+

What is wrong with the last query? I've looked at https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html but can't quite seem to apply it to this problem.

score 1 · Accepted Answer · answered Aug 23 '19 at 00:07

COUNT simply counts non-null values. so both 0 and 1 will get counted. What you need to do is SUM them:

select question_id, 
       sum(if(action = 'show', 1, 0)) as show_count,
       sum(if(action = 'answer', 1, 0)) as answer_count
from survey_log
group by question_id;

Note that since MySQL treats booleans as 0 (false) or 1 (true) in a numeric context, you can remove the if part of the query:

select question_id, 
       sum(action = 'show') as show_count,
       sum(action = 'answer') as answer_count
from survey_log
group by question_id;

MySQL COUNT IF with GROUP BY

1 Answers1