title: mySQL select and group by values
body: <p>I'd like to count and group rows by specific values. This seems fairly simple, but I can't seem to do it.</p>
  
  <p>I have a table set up similar to this:</p>
  
  <pre><code>Table: Ratings
  id pID uID rating
  1  1   2     7
  2  1   7     7
  3  1   5     4
  4  1   1     1
  </code></pre>
  
  <p>id is the primary key, piD and uID are foreign-keys. Rating contains values between 1 and 10, and only between 1 and 10. </p>
  
  <p>I want to run some statistics and count the number of ratings with a certain value. In the example above, two have left a rating of 7.</p>
  
  <p>So I wrote the following query:</p>
  
  <pre><code>SELECT COUNT(*) AS 'count' , 'rating'
  FROM 'ratings'
  WHERE pID= '1'
  GROUP BY `rating`
  ORDER BY `rating`
  </code></pre>
  
  <p>Which yields the nice result as:</p>
  
  <pre><code>count  ratings
  1      1
  1      4
  2      7
  </code></pre>
  
  <p>I'd like to get the mySQL query to include values between 1 and 10 as well.</p>
  
  <p>For example:</p>
  
  <pre><code>Desired Result
  count  ratings
  1      1
  0      2
  0      3
  1      4
  0      5
  0      6
  2      7
  0      8
  0      9
  0      10
  </code></pre>
  
  <p>Unfortunately, I'm relatively new to SQL and I've been reading through everything I could get my hands on for the past hour, but I can't get it to work. I've been leaning along the lines of a some type of JOIN. </p>
  
  <p>If anyone can point me in the right direction, it'd be appreciated.</p>
  
  <p>Thanks.</p>