3

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.

4
  • Try to replace the aliases with full formulas - 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.
    – jkavalik
    Feb 7, 2016 at 9:26
  • Any chance wp_imdb_rating is a view?
    – jkavalik
    Feb 7, 2016 at 11:11
  • @jkavalik I was working on a script similar to imdb. I have post_id, user_id and vote columns inside wp_imdb_rating table. I applied formula that imdb uses in its top system to define movie rating weight. What else infromation you specially want I can post here. Feb 7, 2016 at 21:56
  • Nothing more needed. I just had some idea where the limitation might come from and wanted to check. If the full expression is allowed but the aliases no, then it might be imho classified as a bug? From what I gathered there used to be limitations which did not allow even the full version, so maybe the check was just not fully removed? But just guessing here..
    – jkavalik
    Feb 8, 2016 at 6:24

2 Answers 2

1

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)
0

Another solution, probably with the same performance:

SELECT *
    FROM (
        SELECT post_id,
               COUNT(IF(vote > 0, vote, NULL) AS voters,
               AVG(IF(vote > 0, vote, NULL) AS average
            FROM wp_imdb_rating
            GROUP BY post_id 
          )
    ORDER BY voters * average DESC
    LIMIT 0, 100

Note: Whenever ORDER BY is not the same as the GROUP BY, there will be a "filesort".

1
  • I already used expressions instead of aliases but thanks, this solution works too. Feb 7, 2016 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.