Professional Web Applications Themes

HELP needed: SUM SELECT TOP 5 - MySQL

I have a database with a table called: RESULTS that has the following fields: DIVISION, NAME, SCORE. I want to SUM up the top 5 scores for each DIVISION/NAME. I cant seem to get anything to work in MYSQL. I have seen a rank function used Oracle and stuff along that line, but not available on MYSQL. Here is what I tried without any luck: SELECT r1.division, r1.name, (SELECT SUM(r2.score) FROM results r2 WHERE r1.division=r2.division AND r1.name=r2.name GROUP BY r2.division, r2.name ORDER BY r2.division, r2.name, r2.score DESC LIMIT 5) AS total FROM results r1 ORDER BY r1.division, r1.name, total DESC; ...

  1. #1

    Default HELP needed: SUM SELECT TOP 5

    I have a database with a table called: RESULTS that has the following
    fields: DIVISION, NAME, SCORE. I want to SUM up the top 5 scores for
    each DIVISION/NAME. I cant seem to get anything to work in MYSQL. I
    have seen a rank function used Oracle and stuff along that line, but
    not available on MYSQL. Here is what I tried without any luck:

    SELECT r1.division, r1.name,
    (SELECT SUM(r2.score) FROM results r2 WHERE
    r1.division=r2.division AND r1.name=r2.name
    GROUP BY r2.division, r2.name
    ORDER BY r2.division, r2.name, r2.score DESC LIMIT 5) AS total
    FROM results r1
    ORDER BY r1.division, r1.name, total DESC;


    Can anyone get something like this working on MYSQL v5.0? Help.

    lefebvre@iwavesolutions.com Guest

  2. #2

    Default Re: HELP needed: SUM SELECT TOP 5

    On 18 Jun, 10:06, com wrote: 

    Always a good idea to search the group first:

    http://groups.google.co.uk/group/comp.databases.mysql/browse_frm/thread/6257679aa9e05558/2da151f4fe4e03d0?lnk=gst&q=rank&rnum=5#2da151f4fe4 e03d0

    Captain Guest

  3. #3

    Default Re: HELP needed: SUM SELECT TOP 5

    On Mon, 18 Jun 2007 02:06:50 -0700, com wrote:
     

    Your LIMIT may not limit the right thing : it will limit the number of
    lines you will get in your inner SELECT, but you will anyways get only
    one because of your two clauses in the WHERE.

    I think it would be more tidy to do it with a FUNCTION.

    CREATE FUNCTION `sum_of_five`(_division INT,
    _name VARCHAR(100)) RETURNS int(11)
    BEGIN
    DECLARE _res INT;

    SELECT SUM(score) INTO _res
    FROM (
    SELECT r2.score AS FROM results r2
    WHERE r1.division=r2.division AND r1.name=r2.name
    ORDER BY r2.score DESC LIMIT 5
    ) AS tmptable;

    RETURN _res;

    END$$

    And then :

    SELECT r1.division, r1.name, sum_of_five(division,name)
    FROM results r1
    ORDER BY r1.division, r1.name, total DESC;

    (Not tested, maybe some typos, but it's the idea.)
    subtenante Guest

  4. #4

    Default Re: HELP needed: SUM SELECT TOP 5

    On Mon, 18 Jun 2007 17:39:28 +0800, subtenante
    <com> wrote:

     

    I was right i made some mistakes ! Corrected.
    subtenante Guest

  5. #5

    Default Re: HELP needed: SUM SELECT TOP 5

    On Jun 17, 11:45 pm, subtenante <com> wrote: 
    >
    > I was right i made some mistakes ! Corrected.[/ref]


    Thank you for ALL your help. How can this be accomplished NOT using a
    FUNCTION? My ISP does not allow FUNCTIONS on MySQL for security
    reasons (not sure why thats an issue) but none the less, I appreciate
    any insite to your solution without functions.

    Here is a test environment setup. See if this can be done with TOP 3
    just for brevity sake:

    DROP TABLE games;
    CREATE TABLE games (
    pkey int(11) NOT NULL auto_increment,
    division varchar(15),
    user varchar(15),
    payout int,
    PRIMARY KEY (pkey)
    );

    INSERT INTO games (division, user, payout) VALUES ('A','fred',11);
    INSERT INTO games (division, user, payout) VALUES ('A','fred',2);
    INSERT INTO games (division, user, payout) VALUES ('A','fred',13);
    INSERT INTO games (division, user, payout) VALUES ('A','fred',4);
    INSERT INTO games (division, user, payout) VALUES ('A','fred',5);
    INSERT INTO games (division, user, payout) VALUES ('A','matt',1);
    INSERT INTO games (division, user, payout) VALUES ('A','matt',21);
    INSERT INTO games (division, user, payout) VALUES ('A','matt',31);
    INSERT INTO games (division, user, payout) VALUES ('A','matt',14);
    INSERT INTO games (division, user, payout) VALUES ('A','matt',5);
    INSERT INTO games (division, user, payout) VALUES ('B','tom',13);
    INSERT INTO games (division, user, payout) VALUES ('B','tom',20);
    INSERT INTO games (division, user, payout) VALUES ('B','tom',23);
    INSERT INTO games (division, user, payout) VALUES ('B','tom',9);
    INSERT INTO games (division, user, payout) VALUES ('A','tom',15);
    INSERT INTO games (division, user, payout) VALUES ('B','rick',18);
    INSERT INTO games (division, user, payout) VALUES ('B','rick',29);
    INSERT INTO games (division, user, payout) VALUES ('B','rick',21);
    INSERT INTO games (division, user, payout) VALUES ('B','rick',8);
    INSERT INTO games (division, user, payout) VALUES ('B','rick',25);

    Thanks again!!

    lefebvre@iwavesolutions.com Guest

  6. #6

    Default Re: HELP needed: SUM SELECT TOP 5

    On 19 Jun, 01:55, com wrote: 
    > [/ref]

    >
    > Thank you for ALL your help. How can this be accomplished NOT using a
    > FUNCTION? My ISP does not allow FUNCTIONS on MySQL for security
    > reasons (not sure why thats an issue) but none the less, I appreciate
    > any insite to your solution without functions.
    >
    > Here is a test environment setup. See if this can be done with TOP 3
    > just for brevity sake:
    >
    > DROP TABLE games;
    > CREATE TABLE games (
    > pkey int(11) NOT NULL auto_increment,
    > division varchar(15),
    > user varchar(15),
    > payout int,
    > PRIMARY KEY (pkey)
    > );
    >
    > INSERT INTO games (division, user, payout) VALUES ('A','fred',11);
    > INSERT INTO games (division, user, payout) VALUES ('A','fred',2);
    > INSERT INTO games (division, user, payout) VALUES ('A','fred',13);
    > INSERT INTO games (division, user, payout) VALUES ('A','fred',4);
    > INSERT INTO games (division, user, payout) VALUES ('A','fred',5);
    > INSERT INTO games (division, user, payout) VALUES ('A','matt',1);
    > INSERT INTO games (division, user, payout) VALUES ('A','matt',21);
    > INSERT INTO games (division, user, payout) VALUES ('A','matt',31);
    > INSERT INTO games (division, user, payout) VALUES ('A','matt',14);
    > INSERT INTO games (division, user, payout) VALUES ('A','matt',5);
    > INSERT INTO games (division, user, payout) VALUES ('B','tom',13);
    > INSERT INTO games (division, user, payout) VALUES ('B','tom',20);
    > INSERT INTO games (division, user, payout) VALUES ('B','tom',23);
    > INSERT INTO games (division, user, payout) VALUES ('B','tom',9);
    > INSERT INTO games (division, user, payout) VALUES ('A','tom',15);
    > INSERT INTO games (division, user, payout) VALUES ('B','rick',18);
    > INSERT INTO games (division, user, payout) VALUES ('B','rick',29);
    > INSERT INTO games (division, user, payout) VALUES ('B','rick',21);
    > INSERT INTO games (division, user, payout) VALUES ('B','rick',8);
    > INSERT INTO games (division, user, payout) VALUES ('B','rick',25);
    >
    > Thanks again!![/ref]

    Errm, I already gave you a link to the article that explains it. Why
    are you asking again?

    Captain Guest

Similar Threads

  1. Replies: 4
    Last Post: April 4th, 08:21 AM
  2. Select DISTINCT issue (SQL Expert needed)
    By BP Prgm in forum ASP Database
    Replies: 2
    Last Post: October 2nd, 01:36 AM
  3. Replies: 0
    Last Post: September 11th, 12:19 AM
  4. Replies: 0
    Last Post: April 15th, 01:22 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