title: mysql SELECT COUNT(*) ... GROUP BY ... not returning rows where the count is zero
body: <pre><code>SELECT student_id, section, count( * ) as total
  FROM raw_data r
  WHERE response = 1
  GROUP BY student_id, section
  </code></pre>
  
  <p>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).</p>
  
  <p>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?</p>
  
  <p>Here's what my result set looks like:</p>
  
  <pre><code>student_id  section     total
  1           DAP--29     3
  1           MEA--16     2
  1           NNR--13     1  --&gt; missing the 4th section for student #1
  2           DAP--29     1
  2           MEA--16     4
  2           NNR--13     2  --&gt; missing the 4th section for student #2
  3           DAP--29     2
  3           MEA--16     3
  3           NNR--13     3 --&gt; missing the 4th section for student #3
  4           DAP--29     5
  4           DAP--30     1
  4           MEA--16     1
  4           NNR--13     2 --&gt; here, all 4 sections show up because student 4 got at least one question right in each section
  </code></pre>
  
  <p>Thanks for any insight!</p>
  
  <p>UPDATE: I tried </p>
  
  <pre><code> SELECT student_id, section, if(count( * ) is null, 0, count( * ))  as total
  </code></pre>
  
  <p>and that didn't change the results at all. Other ideas?</p>
  
  <p>UPDATE 2: I got it working thanks to the response below:</p>
  
  <pre><code> 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
  </code></pre>