mySQL - sum.. group by.. order by

Dr Pepper

Hi,

I have a table called gplays which records songid's and the number of plays they get on different types of jukebox:

select songid,sum(plays) from gplays 
group by songid order by sum(plays) desc;
// Produces errer: Invalid use of group function

The problem is with the sum() being included in the 'order by' clause. Anybody know how I can do this? Thanks

bonkey

I dunno MySQL too well, but in other databases the ORDER BY can contain numeric values corresponding to the columns, and can also refer to columns by their aliases.

So the following might work:

select   songid,
         sum(plays) as sum_of_plays
from     gplays 
group by songid 
order by sum_of_plays desc;

Alternately, if numeric positions are valid, you could use "order by 2 desc"

jc

Dr Pepper

Spot on thanks.. Works when you use the alias but not when you try to use the function in the order by clause.. Just figured it out myself 2 minutes ago!

Damn, that was annoying!!