I am building a ranking algorithm using IMDB's formula:
The formula for calculating the Top Rated 250 Titles gives a true Bayesian estimate: weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C where: * R = average for the movie (mean) = (Rating) * v = number of votes for the movie = (votes) * m = minimum votes required to be listed in the Top 250 (currently 1300) * C = the mean vote across the whole report (currently 6.8) for the Top 250, only votes from regular voters are considered.
The problem I am having is that I am not sure how to get the AVG and MAX from the whole result set. SQLFiddle here
I am guessing it has to do with the
GROUP BY clause.
Also, how can I set "business 4" ranking to 0 instead of null?