[snip]
What do you mean by weighted? Taking into account the number of votes for
each and then weighting them against each other?
[/snip]
Yes, essentially each individual movie will show its raw average (people
think this movie is a 7/10) while an overall top rated list will take into
affect that 10,000 people voted on Road to Perdition while one person voted
for Freddie got Fingered and gave it a 10. Obviously, Freddie's not the most
popular film even though its average is 10.
I created a dummy table for this and used your select statement:
mysql> SELECT titleid, (AVG(rating) / COUNT(titleid)) AS weighted
-> FROM movieratings
-> GROUP BY titleid;
+---------+----------+
| titleid | weighted |
+---------+----------+
| 1 | 0.288000 |
| 9 | 0.072748 |
| 19 | 1.250000 |
| 21 | 1.800000 |
| 31 | 8.000000 |
| 199 | 1.000000 |
I think I put my slow brain in today...should I be using an order by?
Here's the same data showing the number of votes:
+---------+-------+----------+
| titleid | votes | weighted |
+---------+-------+----------+
| 1 | 25 | 0.288000 |
| 9 | 99 | 0.072748 |
| 19 | 8 | 1.250000 |
| 21 | 5 | 1.800000 |
| 31 | 1 | 8.000000 |
| 199 | 10 | 1.000000 |
+---------+-------+----------+
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
