mySQL select and group by values
- by Foo
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.
