mysql SELECT COUNT(*)... GROUP BY ...不返回计数为零的行 [英] mysql SELECT COUNT(*) ... GROUP BY ... not returning rows where the count is zero

问题描述

SELECT student_id, section, count( * ) as total
FROM raw_data r
WHERE response = 1
GROUP BY student_id, section

考试共有4个部分，每个部分都有不同数量的问题.我想知道，对于每个学生和每个部分，他们正确回答了多少个问题(响应= 1).

There are 4 sections on the test, each with a different number of questions. I want to know, for each student, and each section, how many questions they answered correctly (response=1).

但是，通过此查询，如果学生在给定的部分中没有正确的问题，则该行将完全不在我的结果集中.我如何确保每位学生总是返回4行，即使一行的总计"为0?

However, with this query, if a student gets no questions right in a given section, that row will be completely missing from my result set. How can I make sure that for every student, 4 rows are ALWAYS returned, even if the "total" for a row is 0?

这是我的结果集的样子:

Here's what my result set looks like:

student_id  section     total
1           DAP--29     3
1           MEA--16     2
1           NNR--13     1  --> missing the 4th section for student #1
2           DAP--29     1
2           MEA--16     4
2           NNR--13     2  --> missing the 4th section for student #2
3           DAP--29     2
3           MEA--16     3
3           NNR--13     3 --> missing the 4th section for student #3
4           DAP--29     5
4           DAP--30     1
4           MEA--16     1
4           NNR--13     2 --> here, all 4 sections show up because student 4 got at least one question right in each section

感谢您的见解！

更新:我尝试过

 SELECT student_id, section, if(count( * ) is null, 0, count( * ))  as total

，这完全没有改变结果.还有其他想法吗?

and that didn't change the results at all. Other ideas?

更新2:由于以下响应，我可以正常工作了:

UPDATE 2: I got it working thanks to the response below:

 SELECT student_id, section, SUM(CASE WHEN response = '1' THEN 1 ELSE 0 END ) AS total
 FROM raw_data r
 WHERE response = 1
 GROUP BY student_id, section

推荐答案

SELECT student_id, section, sum(case when response=1 then 1 else 0 end) as total
FROM raw_data_r GROUP BY student_id, section

请注意，没有WHERE条件.

这篇关于mysql SELECT COUNT(*)... GROUP BY ...不返回计数为零的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！