mySQL select and group by values

Posted by Foo on Stack Overflow See other posts from Stack Overflow or by Foo
Published on 2010-03-27T05:00:05Z Indexed on 2010/03/27 5:03 UTC
Read the original article Hit count: 196

Filed under:
|

I'd like to count and group rows by specific values. This seems fairly simple, but I can't seem to do it.

I have a table set up similar to this:

Table: Ratings
id pID uID rating
1  1   2     7
2  1   7     7
3  1   5     4
4  1   1     1

id is the primary key, piD and uID are foreign-keys. Rating contains values between 1 and 10, and only between 1 and 10.

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.

So I wrote the following query:

SELECT COUNT(*) AS 'count' , 'rating'
FROM 'ratings'
WHERE pID= '1'
GROUP BY `rating`
ORDER BY `rating`

Which yields the nice result as:

count  ratings
1      1
1      4
2      7

I'd like to get the mySQL query to include values between 1 and 10 as well.

For example:

Desired Result
count  ratings
1      1
0      2
0      3
1      4
0      5
0      6
2      7
0      8
0      9
0      10

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.

If anyone can point me in the right direction, it'd be appreciated.

Thanks.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql