I want to create a rating weight and I need do calculate AVG, then find COUNT and multiply both to get weight. I have something like that:
SELECT post_id, COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END) AS voters,
AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END) AS average FROM wp_imdb_rating
GROUP BY post_id
ORDER BY voters * average DESC
LIMIT 0, 100
But it says:
#1247 - Reference 'voters' not supported (reference to group function)
I can order by average and voters separately, but I can't combine them with any formula. How can I use a formula in order clause that includes values from AVG and COUNT?
Thanks.
ORDER BY COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END) * AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)
- either it will work or you might get some other error message, hopefuly more helpful this time.wp_imdb_rating
is a view?