List:General Discussion« Previous MessageNext Message »
From:Ian M. Evans Date:July 15 2002 10:43pm
Subject:RE: Weighted average
View as plain text  
-----Original Message-----
From: Andrew K-C [mailto:zuckuss@stripped]
you might be able to do a "ORDER BY weighted DESC" to get them to come out
in order of weight
----------------------------
Thanks...I'll give that a try...

Hmm, the only problem with that query is that it doesn't get it quite right:

mysql> SELECT titleid, AVG(rating), COUNT(titleid) AS votes ,(AVG(rating) /
COUNT(titleid)) AS weighted FROM movieratings GROUP BY titleid order by
weighted desc;
+---------+-------------+-------+----------+
| titleid | AVG(rating) | votes | weighted |
+---------+-------------+-------+----------+
|      31 |      8.0000 |     1 | 8.000000 |
|      21 |      9.0000 |     5 | 1.800000 |
|      19 |     10.0000 |     8 | 1.250000 |
|     199 |     10.0000 |    10 | 1.000000 |
|       1 |      7.2000 |    25 | 0.288000 |
|       9 |      7.2020 |    99 | 0.072747 |
|    2199 |      1.0000 |    79 | 0.012658 |
+---------+-------------+-------+----------+

Title 31 shouldn't be #1 with only one vote.

Doing it by ASC isn't any better as it puts 2199 first (79 votes 1/10
rating)

Scratching head...

Thread
Weighted averageIan M. Evans15 Jul
  • RE: Weighted averageJay Blanchard15 Jul
    • RE: Weighted averageIan M. Evans15 Jul
      • RE: Weighted averageJay Blanchard15 Jul
RE: Weighted averageIan M. Evans16 Jul
  • Re: Weighted averageMark Matthews16 Jul
Re: Weighted averageIan M. Evans16 Jul
Re: Weighted averageShashank Tripathi16 Jul
Re: Weighted averageShashank Tripathi16 Jul
Re: Weighted averageMark Matthews16 Jul
  • RE: Weighted averageIan M. Evans17 Jul