MySQL Bugs: #5478: order by sum(xxx) fails but order by N on selected sum(xxx) works

Bug #5478	order by sum(xxx) fails but order by N on selected sum(xxx) works
Submitted:	8 Sep 2004 16:07	Modified:	24 Nov 2004 18:15
Reporter:	Keith Bremer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3 beta	OS:	Linux (Linux (2.4.x) (Mandrake 9.2))
Assigned to:		CPU Architecture:	Any

Description:
Order by on a group function (in this case, SUM) failed with error 1111 but when I included the group function in the select list and used order by N (N=selected item no.) it worked as I would expect.  It also works if I give the SUM function in the select list a column alias and order by alias.

I tried the following query (the table is an innodb type):
  select a, b
  from tbl
  group by a, b
  order by sum(c);
and this failed with error ERROR 1111 (HY000): Invalid use of group function

However, when I tried it using:
  select a, b, sum(c)
  from tbl
  group by a, b
  order by 3;
this seemed to work as expected.

This seems to be inconsistent.  Is it a bug or am I experiencing a known limitation?  The second form will provide me a suitable workaround for the moment, so it certainly isn't critical.

How to repeat:
Create an innodb table with columns a, b and c (any data types, but integers will be fine), as follows:
create table tbl (a integer, b integer, c integer) type=innodb;

Populate the table with arbitrary values, although repeated values in a and b will add to the authenticity.
e.g. insert into tbl (a, b, c) values (1,2,3), (1,2,4), (2,3,4), (2,3,5), (2,3,6), etc... ;
Try the queries above.

Suggested fix:
Include the group function in the select list and order by the select list item no. or a column alias on that group function.  (My code needs to discard the value of the group function as I only use it for sequencing in this query, but that's OK.)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You are mixing two different things:

  select a, b
  from tbl
  group by a, b
  order by sum(c);
and this failed with error ERROR 1111 (HY000): Invalid use of group function

This is because the standard does not allow using aggregate function which is not specified in SELECT part of the query.

  select a, b, sum(c)
  from tbl
  group by a, b
  order by 3;

This syntax (ORDER BY ordinal) is deprecated by latest SQL standards.

Instead of it you should use either:

  select a, b, sum(c)
  from tbl
  group by a, b
  order by sum(c);

  select a, b, sum(c) AS sum_alias
  from tbl
  group by a, b
  order by sum_alias;

Alexander Keremidarski wrote:

<quote>
Instead of it you should use either:

  select a, b, sum(c)
  from tbl
  group by a, b
  order by sum(c);

  select a, b, sum(c) AS sum_alias
  from tbl
  group by a, b
  order by sum_alias;
</quote>

In MySQL 4.1.7, the first example still fails with error ERROR 1111 (HY000): Invalid use of group function

This is a particularly frustrating problem for users of Hibernate (hibernate.org) where sorting by an alias of the aggregate function is not (yet?) an option.

it is a historical MySQL limitation (inability to use aggregate functions in order by) we plan to lift in in 5.0

Related to this question. 
I have tried to use max and sum in the same query and I get the same error 1111. Is there a way to get the maximum value from summed values?
SELECT max(sum(value)) FROM table GROUP BY name

This can be in the code but I thought the query could be enhanced.