Geek Answers Handbook

Mysql SELECT COUNT (*) ... GROUP BY ... does not return a row where the counter is zero

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

The test has 4 sections, each of which has a different number of questions. I want to know, for each student and each section, how many questions they answered correctly (answer = 1).

However, with this query, if the student has no questions in this section, this line will be completely absent in my result set. How can I make sure that 4 rows are ALWAYS returned for each student, even if "total" for row 0?

Here my result is as follows:

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

Thank you for understanding!

UPDATE: I tried

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

and that did not change the results at all. Other ideas?

UPDATE 2: I got the job thanks to the answer 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
+5
3
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 .

+9
 SELECT r.student_id, 
             r.subject, 
             sum( r.response ) as total
        FROM raw_data r
    GROUP BY student_id, subject
+1

if you have a separate table with student information, you can select students from this table and join the results in the table data_raw:

SELECT si.student_name, rd.student_id, rd.section, rd.count(*) AS total
    FROM student_info AS si LEFT JOIN raw_data AS rd USING rd.student_id = si.student_id

Thus, he first selects all the students, and then runs the count command.

0
source

More articles:


All Articles