0

I'm creating a list for best movies which are based on the users votes like imdb. I have done the list with this sql query:

 SELECT data_id, COUNT(point), SUM(point) 
 FROM voting_table
 WHERE data_type='1' 
 GROUP BY data_id 
 order by SUM(point)/COUNT(point) 
 DESC limit 100

This works well but i want also the number of votes(point) affect the order. I mean if a movie gets average of 8 with 10 votes and another movie gets average of 8 but with 5 votes. The one which has more votes should be listed higher than the other one. How can i do it? And is the query i wrote is efficent for server performance?

echo_Me
  • 37,078
  • 5
  • 58
  • 78
Jenkins
  • 43
  • 1
  • 7

3 Answers3

0

Just add the second order you want separated by comma .try this

 SELECT data_id, COUNT(point), SUM(point) 
 FROM voting_table
 WHERE data_type='1' 
 GROUP BY data_id 
 order by SUM(point)/COUNT(point) ,COUNT(point)
 DESC limit 100
echo_Me
  • 37,078
  • 5
  • 58
  • 78
0

Try changing your order by clause to be:

order by SUM(point)/COUNT(point) desc, COUNT(point) desc

As it stands, your query appears to be efficient.

0

There is function AGV, I suggest you use that.

sort by avg, then by count or sum

...
ORDER BY AVG(point) DESC, COUNT(point) DESC
...

As of performance, there is not much you can do wihout complicating data structure. It should be fine as it is unless your site si going to be as popular as imdb. If your voting table grows past the point where speedup is needed then you need to start precalculating stuff (for real time updates using triggers that update score in movies table or some other intermediate table dedicated for that, other methods)

Imre L
  • 6,159
  • 24
  • 32