MySQL – Creating a Calculated Column with Functions and Count

countfunctionsMySQL

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.

Best Answer

It seems to be some old limitation of the optimizer maybe, aliases to aggregating expressions are not allowed in more complex expressions in ORDER BY.

You can get around it by using the full expression instead of the alias:

ORDER BY
    COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END)
  *
    AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)
Related Question