title: MySQL Query - getting missing records when using group-by
body: <p>I have a query :</p>
  
  <blockquote>
  <pre><code>select score, count(1) as 'NumStudents' from testresults where testid = 'mytestid'
  group by score order by score
  </code></pre>
  </blockquote>
  
  <p>where testresults table contains the performances of students in a test. A sample result looks like the following, assuming maximum marks of the test is 10.</p>
  
  <blockquote>
    <p><strong>score, NumStudents</strong></p>
    
    <p>0 10 <br/> 1 20 <br/> 2 12 <br/> 3 5 <br/> 5 34 <br/> .. <br/> 10 23 <br/></p>
  </blockquote>
  
  <p>As you can see, this query does not return any records for scores which no student have scored. For eg. nobody scored 4/10 in the test and there are no records for score = 4 in the query output. </p>
  
  <p>I would like to change the query so that I can get these missing records with 0 as the value for the NumStudents field. So that my end output would have max + 1 records, one for each possible score. </p>
  
  <p>Any ideas ?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>The database contains several tests and the maximum marks for the test is part of the test definition. So having a new table for storing all possible scores is not feasible. In the sense that whenever I create a new test with a new max marks, I need to ensure that the new table should be changed to contain these scores as well. </p>