mySQL select and group by values

Question

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.

Answer 1

create regular or temporary table with numbers from 1 to 10 and LEFT JOIN it with yours. like:

SELECT COUNT(`rating`) AS `count` , `rating`
FROM `numbers`
LEFT JOIN `ratings` ON `numbers`.`n` = `ratings`.`rating`
WHERE pID= '1'
GROUP BY `numbers`.`n`

ps: ORDER BY here is ambiguous, because by default GROUP BY already sort the results

pps: you have a number of typos in your query with using ' instead of `

Answer 2

You could do this pretty simply in php.

//do mysql query
for ($i=0; $i<10; $i++) {
     //$i + 1 is this rating
     //check if this ratings count is in result array
     //else use 0 as count
     //echo count
} 

Answer 3

I can't think of any function, or methodology, to directly get a range of ratings like what you need. The only thing I can think of is to have a table containing the allowable range of ratings.

Table: AllowedRatings
rating
1
2
3
4
5
6
7
8
9
10

Then use this to do a subquery with your ratings table:

SELECT (SELECT COUNT(*) 
          FROM ratings 
         WHERE ratings.rating = AllowedRatings.rating 
           AND pid = '1'
       )
     , rating
FROM AllowedRatings
ORDER BY rating

I know this could also be done by a join, but that would be more complicated, and I'm honestly a bit too tired to work it out, or set up a DB correctly to test it, but the subquery method should work.

The advantage of this method is that you could put more information in for your ratings, like a description (eg: 1 = 'Sucks', 3 = 'Meh', 5 = 'Ok', 7 = 'Worthwhile', 10 = 'Amazing!') or change your range without having to edit hard-coded values