List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:September 9 2010 2:08pm
Subject:Re: Fwd: Query SUM help
View as plain text  
On 9/9/2010 3:57 AM, Tompkins Neil wrote:
> 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
> 

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