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
Re: Query SUM help
did u try to use LIMIT after ORDER BY On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil neil.tompk...@googlemail.comwrote: 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
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 anan...@gmail.com 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 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
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 neil.tompk...@googlemail.comwrote: 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 anan...@gmail.com 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 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
Re: Fwd: Query SUM help
On 9/9/2010 3:57 AM, Tompkins Neil wrote: 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 The meat of your problem is the top 11 players part. The SQL language operates on sets, not sequences. In order to find the top 11 you need to somehow identify them explicitly so that you can process them as a set of values. Do you have a column on your `players` table that sequences (from 1..n) the players in the order you want them ranked? If not, you will need to add that data to your `players` table (or build a temporary table with that information in it), then pick the top 11, then work on their SUM()-s. Can you not just filter out the top 11 in your client code from the query that includes all players totals? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- 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
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
Query SUM help
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
