Query SUM help

[Shawn Green (MySQL)]

Any help would be really appreciated ?
---------- Forwarded message ----------
Date: Wed, Sep 8, 2010 at 5:30 PM
Subject: Query SUM help
Hi
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

[Travis Ard]

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:***@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 <***@googlemail.com>
Date: Wed, Sep 8, 2010 at 5:30 PM
Subject: Query SUM help
To: "[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