Bug #39455 Would be useful to ORDER BY arithmetic expressions on GROUP BY columns
Submitted: 15 Sep 2008 15:01 Modified: 4 Feb 2009 15:08
Reporter: Chris Wilson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S4 (Feature request)
Version:5.0.51a-3ubuntu5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: order group by summary arithmetic expression
Triage: Needs Triage: D5 (Feature request)

[15 Sep 2008 15:01] Chris Wilson
Description:
Consider the following table:

create table acct_v6(ip_src char(15), ip_dst char(15), bytes int, stamp_inserted datetime);

I can query the table for results grouped by total bytes downloaded:

select
sum(if(ip_src,bytes,0)) AS downloaded,
sum(if(ip_dst,bytes,0)) AS uploaded
FROM acct_v6 GROUP BY ip_src ORDER BY downloaded;

but not by total bytes download + uploaded:

select
sum(if(ip_src,bytes,0)) AS downloaded,
sum(if(ip_dst,bytes,0)) AS uploaded
FROM acct_v6 GROUP BY ip_src ORDER BY downloaded + uploaded;

ERROR 1247 (42S22): Reference 'downloaded' not supported (reference to group function)

How to repeat:
Try the queries above.

Suggested fix:
I guess this would require another stage of arithmetic evaluation and parsing after the GROUP BY phase.
[4 Feb 2009 15:08] Susanne Ebrecht
Many thanks for writing a feature request.

This is related to; it is not possible to use function in order by.

Our development will discuss implementation of it.
[5 Sep 2012 19:55] Sveta Smirnova
Bug #66656 was marked as duplicate one.
[5 Sep 2012 20:09] matteo sisti sette
Another example:

This works as expected:

SELECT username, COUNT( message.id ) AS n_messages
FROM user
LEFT JOIN message ON message.author_user_id = user.id GROUP BY user.id
ORDER BY n_messages DESC 

But this doesn't:

SELECT username, COUNT( message.id ) AS n_messages
FROM user
LEFT JOIN message ON message.author_user_id = user.id GROUP BY user.id
ORDER BY IF (n_messages>0, 0, 1), username DESC 

The second query results in the following error message:

#1247 - Reference 'n_messages' not supported (reference to group function)

Also note that the error message is very cryptic (but this is a general issue with error messages in MySQL)