Query SUM help

--001517503cc827cda1048fc209d9
Content-Type: text/plain; charset=ISO-8859-1

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

--001517503cc827cda1048fc209d9--

Fwd: Query SUM help

--00c09f9e0cb19ff5ff048fcefce2
Content-Type: text/plain; charset=ISO-8859-1

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

--00c09f9e0cb19ff5ff048fcefce2--

Re: Query SUM help

--0016e659f62c148dc3048fcf42d1
Content-Type: text/plain; charset=ISO-8859-1

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
>

--0016e659f62c148dc3048fcf42d1--

Re: Query SUM help

--00c09f8e5fb62976d7048fcf5f76
Content-Type: text/plain; charset=ISO-8859-1

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.tompkins@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
>>
>
>

--00c09f8e5fb62976d7048fcf5f76--

Re: Query SUM help

--0016e65684e6ab81ef048fd3915a
Content-Type: text/plain; charset=ISO-8859-1

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.tompkins@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
>>>
>>
>>
>

--0016e65684e6ab81ef048fd3915a--

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
> 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
>

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=gcdmg-mysql-2@m.gmane.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.tompkins@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=gcdmg-mysql-2@m.gmane.org