>What version of MySQL are you using?
Our host, Interland, is running a pre-3.23 version.
>back, 3.23.x supports them), then just issue a similar query and store
>the result yourself...
Should've thought of that myself. Thanks.
--
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Ian M. Evans wrote:
|
| SELECT @totalVotes:=COUNT(titleid) FROM movieratings
|
|
What version of MySQL are you using? I just tried this on MySQL-4.0.2
and it worked.
If your version of MySQL doesn't support variables (they go pretty far
back, 3.23
Nope, the SQL query I suggested earlier is incorrect because it biases the
display by AVG of votes already. You do need to divide the average by all
votes, as suggested by Mark.
In what environment do you want to use this Ian? Can it not be done with two
queries...if it is in some programming lan
Hi Ian,
Could you give this kludgish SQL query a test-drive and let me know if
it helps? For all I know this may be very silly, in which case apologies for
sticking my neck into this expert discussion...
select
titleid
,avg(rating) as avg
,count(titleid) as votes
,avg
SELECT @totalVotes:=COUNT(titleid) FROM movieratings
Thanks for the reply.
Alas, the above query syntax gives me an error 1064.
--
Ian Evans
Chairman & Executive Producer
Digital Hit Entertainment
http://www.digitalhit.com
-
-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
-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
[snip]
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.0727
[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
[snip]
Not wanting to reinvent the wheel here, I wonder if anyone else has ever
done a rating system.
There'll be two columns: title_id for the title of the movie being rated and
then a rating from one to ten.
What would be the most efficient SQL statement to find a title's weighted
average?
[/s
10 matches
Mail list logo
