SQL: SUM TOP 3 - grouped by categories

[mlefebvre]

Im trying to sum up the top 3 values for each user in each division. I have tried numerous approaches with using LIMIT 3 and have tries using COUNTS, etc... I cant seem to get the query right. Help.

 

 

I have a table as follows:

 

DROP TABLE games;

CREATE TABLE games (

pkey int(11) NOT NULL auto_increment,

division varchar(15),

user varchar(15),

payout int,

PRIMARY KEY (pkey)

);

 

with the following data:

 

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',;

INSERT INTO games (division, user, payout) VALUES ('B','rick',25);

 

Im trying to get output such as:

 

division user total

A fred 29

A matt 66

A tom 15

B rick 75

B tom 56

 

 

I have tried the following querry but without any luck:

 

SELECT x.division, x.user, SUM(x.payout) AS total

FROM games AS x

WHERE (

SELECT count(*)

FROM games

WHERE division = x.division

AND user = x.user

AND payout > x.payout

) < 3

GROUP

BY x.division, x.user

ORDER

BY x.division, x.user, total DESC;

 

and I have tried:

 

SELECT x.division, x.name, SUM(t.payout) AS total FROM

(SELECT division, name, payout

FROM games

WHERE division=x.division AND name=x.name

ORDER BY division, name, payout LIMIT 3) AS t, games AS x

WHERE t.division=x.division AND t.name=x.name

GROUP BY x.division, x.name

ORDER BY x.division, x.name, total;

 

 

None work. What am I doing wrong? Is there a better way without using FUNCTIONS? I appreciate your help!!

 

[bubblegum.anarchy]

This was extremely tough:

 

SELECT games.division, games.user, sum(games.payout) 
FROM games
     INNER JOIN (
          SELECT substring_index(group_concat(pkey ORDER BY games.division, games.user, games.payout DESC), ',', 3) AS top_three_pkey
          FROM games
          GROUP BY games.division, games.user
          ORDER BY games.division, games.user, games.payout
     ) AS top_three ON find_in_set(games.pkey, top_three_pkey)
GROUP BY games.division, games.user

[Illusion]

I don't know it works or not but give a try

SELECT x.division, x.user, SUM(x.payout) AS total FROM
(SELECT division, user
FROM games
WHERE user=ALL (select DISTINCT user from games)
ORDER BY division, user, payout LIMIT 3) AS t, games AS x
WHERE t.division=x.division AND t.user=x.user
GROUP BY x.user
ORDER BY x.division, x.user, total;