Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

mySQL - sum.. group by.. order by

  • 13-04-2006 11:24AM
    #1
    Dr Pepper
    Registered Users, Registered Users 2 Posts: 821 ✭✭✭


    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


Comments

  • #2
    bonkey
    Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭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


  • #3
    Dr Pepper
    Registered Users, Registered Users 2 Posts: 821 ✭✭✭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!! :D


Advertisement