MySQL doesn't have the windowing functions that some other databases provide, but you can probably achieve the same effect with a couple user-defined variables:
select teams_id as my_teams_id ,sum(rating) as total_team_rating from (select players.teams_id ,players.players_id ,players_master.rating ,if(@team <> players.teams_id, @row := 1, @row := @row + 1) as rank, @team := players.team_id from players join players_master on players.players_id = players_master.players_id where players.worlds_id = 1 and players.red_cards = 0 and players.injury_duration_remaining = 0 order by players.teams_id, players_master.rating desc) s1 where rank <= 11 group by teams_id; -Travis -----Original Message----- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, September 09, 2010 1:58 AM To: [MySQL] Subject: Fwd: Query SUM help Any help would be really appreciated ? ---------- Forwarded message ---------- From: Tompkins Neil <neil.tompk...@googlemail.com> Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: "[MySQL]" <mysql@lists.mysql.com> Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org