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?