MySQL grouping problem

I have a sports tracker MySQL database and am trying to extract some stats; relevant tables are outlined below:

game

id bigint(20) unsigned PK

round_id bigint(20)

team_1_id bigint(20) unsigned

team_2_id bigint(20) unsigned

date datetime

team

id bigint(20) unsigned PK

name varchar(128)

player (not really required for this query)

id bigint(20) unsigned PK

first_name varchar(128)

surname varchar(128)

dob datetime

player_team_game

id bigint(20) unsigned PK

game_id bigint(20) unsigned

player_id bigint(20) unsigned

team_id bigint(20)

stats

id bigint(20) PK

player_team_game_id bigint(20)

kicks int(11)

goals int(11)

tackles int(11)

The intent is to determine total kicks, goals and tackles for each team in each game. An example resultset might look like the following:

game.id    | game.date    | team.name    | stats.kicks    | stats.goals    | stats.tackles  
1          | 2011-01-01   | team1        | 25             | 30             | 35  
1          | 2011-01-01   | team2        | 26             | 31             | 36  
2          | 2011-01-01   | team3        | 27             | 32             | 37  
2          | 2011-01-01   | team4        | 28             | 33             | 38  
3          | 2011-01-02   | team5        | 29             | 34             | 39  
3          | 2011-01-02   | team6        | 30             | 35             | 40  

Can anyone help me with an appropriate query?

Edit: potentially I haven't made the problem clear; the issue is that the game table has both game.team_id_1 and game.team_id_2 so grouping needs to reflect the开发者_StackOverflow中文版 results for each team in separate recordsets.

Try this:

SELECT
    g.id, g.date,
    t.name,
    SUM(s.kicks),
    SUM(s.goals), 
    SUM(s.tackles)
FROM stats s
LEFT JOIN player_team_game ptg ON ptg.id = s.player_team_game_id
LEFT JOIN game g ON g.id = ptg.game_id
LEFT JOIN team t ON t.id = ptg.team_id
GROUP BY ptg.team_id
ORDER BY g.id, t.id

I would think that the below should give the result that you want.

select game.id, game.date, team.name, 
sum (stats.kicks), sum (stats.goals), sum (stats.tackles)
from game, player_team_game, stats, team
where player_team_game.game_id = game.id
and player_team.game.team_id = team.id
and player_team_game.id = stats.player_team_game_id
group by game.id, game.date, team.name