-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Ian M. Evans wrote:
| -----Original Message-----
| From: Andrew K-C [mailto:[EMAIL PROTECTED]]
| 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...
(AVG(rating) / COUNT(titleid)) appears that it is using the count when
grouped-by (which is to be expected, because that's how it's supposed to
work).
If MySQL had subqueries (they're coming in 4.1), you could get the total
of all possible votes.
Now it appears you will need two queries to do it, i.e.:
SELECT @totalVotes:=COUNT(titleid) FROM movieratings
which will get you the total votes across all titles, then:
SELECT titleid, AVG(rating), COUNT(titleid) AS votes ,(AVG(rating) /
~ @totalVotes) AS weighted FROM movieratings GROUP BY titleid order by
~ weighted desc;
-Mark
- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma
~ __ ___ ___ ____ __
~ / |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]>
~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
~ <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6-2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE9M4KdtvXNTca6JD8RAguPAJ0WgiX7S7sdp+pANZKFVJl6ZrrncwCgloY0
rT6f9sqpRE5bbRd3JelvmKI=
=XMz/
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
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
