RE: Query SUM help
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 Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: "[MySQL]" 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
Re: Query SUM help
try using the RANK function... something like select * from table order by RANK desc limit 11.this will get u the top 11 rows. regards anandkl On Thu, Sep 9, 2010 at 1:55 PM, Tompkins Neil wrote: > Yes, but it doesn't work. Basically I want the SUM(players_master.rating) > only to SUM the top 11 players from each team. Any suggestions ? > > Cheers > Neil > > > On Thu, Sep 9, 2010 at 9:17 AM, Ananda Kumar wrote: > >> did u try to use LIMIT after ORDER BY >> >> >> On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil < >> neil.tompk...@googlemail.com> wrote: >> >>> Any help would be really appreciated ? >>> >>> >>> >>> -- Forwarded message -- >>> From: Tompkins Neil >>> Date: Wed, Sep 8, 2010 at 5:30 PM >>> Subject: Query SUM help >>> To: "[MySQL]" >>> >>> >>> 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 >>> >> >> >
Re: Query SUM help
Yes, but it doesn't work. Basically I want the SUM(players_master.rating) only to SUM the top 11 players from each team. Any suggestions ? Cheers Neil On Thu, Sep 9, 2010 at 9:17 AM, Ananda Kumar wrote: > did u try to use LIMIT after ORDER BY > > > On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Any help would be really appreciated ? >> >> >> >> -- Forwarded message -- >> From: Tompkins Neil >> Date: Wed, Sep 8, 2010 at 5:30 PM >> Subject: Query SUM help >> To: "[MySQL]" >> >> >> 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 >> > >
Re: Query SUM help
did u try to use LIMIT after ORDER BY On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil wrote: > Any help would be really appreciated ? > > > > -- Forwarded message -- > From: Tompkins Neil > Date: Wed, Sep 8, 2010 at 5:30 PM > Subject: Query SUM help > To: "[MySQL]" > > > 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 >
