[thelist] [MySQL] need help with a query

[Chris Johnston]

I have a table that holds movie ratings. Each record contains a rating 
out of 5 and a comment, along with some other information.

+------------+------------------+
|Field       | Type             |
+------------+------------------+
|idrating    | int(10) unsigned |
|iduser      | int(10) unsigned |
|idmovie     | int(10) unsigned |
|user_rating | int(10) unsigned |
|comment     | text             |
|rating_date | date             |
+------------+------------------+

What I am looking to generate is a table/view/query that would count all 
the occurances of a rating (1 - 5) and tell me how many of each appear 
for a single movie. So, for example, something like this:

+-----------+---+---+---+---+---+---+---+-----+---+--------+
|Movie      | r | c | r | c | r | c | r | c   | r | c      |
+-----------+---+---+---+---+---+---+---+-----+---+--------+
|Casablanca | 1 | 5 | 2 | 7 | 3 | 8 | 4 | 204 | 5 | 204589 |
+-----------+---+---+---+---+---+---+---+-----+---+--------+

(this is not necessarily the format of the table that I am looking for, 
just using it for illustration purposes. I realize that it breaks just 
about every Normal Form out there for a table)

This would tell me that Casablanca has had 5 people rate it 1/5, 7 
people rate it 2/5, 8 people rate it 3/5, and so on. Then, using this 
information, I could calculate the percentages of each rating.

Currently, I am using sql to get all the comments for a particular movie 
and then looping through the record set and incrementing array values 
using the rating as the array index. But there has to be an easier and 
more efficient way than that.

Unfortunetaly, this is a little beyond my sql skills at the moment.

Thanks in advanced,

chris johnston