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!!