Bug #6805 order by aggregate functions fail
Submitted: 24 Nov 2004 16:43 Modified: 24 Nov 2004 18:25
Reporter: jt k Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux 2.6.9)
Assigned to: CPU Architecture:Any

[24 Nov 2004 16:43] jt k
Description:
Order by aggregate functions fail with error 'ERROR 1111 (HY000): Invalid use of group function' even when the same aggregate function is one of the selected columns as required by the specification.  This bug was previously reported on MySQL 4.1.3 beta as Bug #5478 but was marked as 'Not a Bug' and this bug still exists in the current production version 4.1.7.

How to repeat:
Any query with an aggregate function in the ORDER BY clause will give the error 'ERROR 1111 (HY000): Invalid use of group function'.

For example, the query provided by Alexander Keremidarski as a solution to Bug #5478 causes the error:
  select a, b, sum(c)
  from tbl
  group by a, b
  order by sum(c);

Suggested fix:
There is currently a workaround which sorts an alias of the aggregate function.  Most people are happy with this workaround which may be the reason that Bug #5478 was never resolved.

The workaround, also provided by Alexander Keremidarski in response to Bug #5478:
  select a, b, sum(c) AS sum_alias
  from tbl
  group by a, b
  order by sum_alias;

It would be preferable to be able to sort by aggregate functions and not be required to use an alias.
[24 Nov 2004 18:25] Sergei Golubchik
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

http://bugs.mysql.com/bug.php?id=5478