List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:September 9 2010 5:10pm
Subject:RE: Query SUM help
View as plain text  
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.tompkins@stripped] 
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.tompkins@stripped>
Date: Wed, Sep 8, 2010 at 5:30 PM
Subject: Query SUM help
To: "[MySQL]" <mysql@stripped>


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

Thread
Query SUM helpTompkins Neil8 Sep
  • Fwd: Query SUM helpTompkins Neil9 Sep
    • Re: Query SUM helpAnanda Kumar9 Sep
      • Re: Query SUM helpTompkins Neil9 Sep
        • Re: Query SUM helpAnanda Kumar9 Sep
    • Re: Query SUM helpAnanda Kumar9 Sep
    • Re: Query SUM helpAnanda Kumar9 Sep
    • Re: Fwd: Query SUM helpMySQL)9 Sep
    • RE: Query SUM helpTravis Ard9 Sep