Professional Web Applications Themes

top three aggregate sum function - MySQL

I have a table that collects the scores of players called "results". e.g. Bob 45, Jon 55, Tom 34, Tom 44, Bob 70, Tom 54, etc Each player can have as many scores listed as they like. What I would like is to simply produce the top three sum per player and order by that sum. Loosely speaking what I want is the following query... SELECT name, topThreeSum FROM results GROUP BY name ORDER BY topThreeSum DESC I posted a similar question a couple months back and Strawberry (thanks by the way) gave me some code to play with but ...

Sponsored Links
  1. #1

    Default top three aggregate sum function

    I have a table that collects the scores of players called "results".

    e.g.

    Bob 45,
    Jon 55,
    Tom 34,
    Tom 44,
    Bob 70,
    Tom 54,
    etc

    Each player can have as many scores listed as they like. What I would
    like is to simply produce the top three sum per player and order by
    that sum. Loosely speaking what I want is the following query...

    SELECT name, topThreeSum
    FROM results
    GROUP BY name
    ORDER BY topThreeSum DESC

    I posted a similar question a couple months back and Strawberry
    (thanks by the way) gave me some code to play with but I couldn't make
    head or tail of it.

    Many thanks in advance,

    Geoff.

    Sponsored Links
    Geoff Guest

  2. #2

    Default Re: top three aggregate sum function

    I have just seen a similar question from alemare from April the 4th.
    Again, the answer given was too specific. Where in the manual should
    one look to find an appropriate example? Under what should I search
    for?

    Many thanks again,

    Geoff.

    Geoff Guest

  3. Moderated Post

    Default Re: top three aggregate sum function

    Removed by Administrator
    strawberry Guest
    Moderated Post

  4. #4

    Default Re: top three aggregate sum function

    Hi Strawberry,

    Thanks for reply. I have tried running the query (I cut out all to do
    with id as I don't have this in my table) but it doesn't give the
    correct output. Perhaps something is wrong. From what I understand of
    the query you somehow recurse over all names and compare each score
    per player against every other score of that player. This produces
    multiples of certain entires and then by grouping them together and
    counting their instances this produces a rank per player for his
    scores? Am I correct? Is this id stuff needed? Are we counting up at
    the wrong place? Isn't this a heavy way to answer what I thought is a
    simple query, or is that just another example of "that's life"? Is
    there a way to do this with user defined functions? Finally, would you
    use this method to say rank the players by their third best result?

    Plenty questions, plenty of thanks.

    Geoff.

    Geoff Guest

  5. #5

    Default Re: top three aggregate sum function

    On 10 Apr, 12:30, "Geoff" <co.uk> wrote: 

    Of course something is wrong, otherwise it would be working!

    The problem is that you have cut out the part with the ID. You need an
    ID to make this work properly as Strawberry explained.

    Captain Guest

  6. Moderated Post

    Default Re: top three aggregate sum function

    Removed by Administrator
    Geoff Guest
    Moderated Post

  7. #7

    Default Re: top three aggregate sum function

    On Apr 10, 12:30 pm, "Geoff" <co.uk> wrote: 

    Yes, due to the way I've constructed the query, the id stuff is
    needed. We need some system of identifying a row as being somehow
    distinct from another row - especially in the event of the same player
    obtaining the same score twice. It also allows for a row to, in a
    sense, recognise itself when the table is joined to itself.

    You should always have some kind of PRIMARY KEY on your tables -
    although it doesn't need to be a dedicated 'id' column, as in my
    example. The PRIMARY KEY could be a combination of the player's name
    and the date that they made that score - but the id column is a simple
    way to maintain table integrity, and it actually simplifies the query
    as fewer comparisons have to be made.

    It's easy to add an id column, just use this syntax - although only do
    this if you really don't have a PK on the table already:

    ALTER TABLE `my_table` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY
    KEY FIRST

    Finally, of course there probably are other ways of constructing the
    query, but I'd be impressed if they were significantly faster, and,
    although I can't imagine why you'd want to rank players by their third
    best result, I think it would be very straightforward to adapt the
    present query - but I'll leave that as an exercise for the reader.

    strawberry Guest

  8. #8

    Default Re: top three aggregate sum function

    > It's easy to add an id column, just use this syntax - although only do 

    Aha, I do have a primary key based on date/name (no player can play
    more than once per day). How do I squeeze that into the query?
     

    Good points. It's just that I am surprised that this is the way to
    answer the original question. Although not a real query one must think
    that it is an awful lot of work to compute just the third best result.
    (answer for reader: set rank = 3 ?).

    Thanks again for the patience,

    Geoff.

    Geoff Guest

  9. #9

    Default Re: top three aggregate sum function

    I just wanted to say that I got it all working. Thanks for all your
    help.

    Geoff.


    Geoff Guest

Similar Threads

  1. Replies: 7
    Last Post: April 6th, 05:47 PM
  2. Aggregate Function Nightmare
    By Henweigh99 in forum Macromedia ColdFusion
    Replies: 15
    Last Post: July 14th, 02:54 AM
  3. Aggregate Bitwise OR Function
    By Mark in forum IBM DB2
    Replies: 3
    Last Post: September 25th, 06:53 PM
  4. Replies: 3
    Last Post: July 4th, 01:53 PM
  5. note 30395 rejected from function.aggregate by didou
    By didou@php.net in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 12:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139