calculating player statistics from database

Question

i have the following tables.

i want to fetch all the player statistics record from the given tables, the records of individual player includes.

1. Player Name
2. Position
3. Total Number of games played
4. Number of goals scored
5. Total number of assist for goals.
6. Total Points (total goals + total assist = total points).

after trying i came up with this query

SELECT SQL_CALC_FOUND_ROWS
    CONCAT(u.first_name, ' ', u.last_name) as player_name,
    p.position, 
    COUNT(g.id)
FROM 
    gce_player p 
LEFT JOIN 
    gce_user u ON(u.id = p.user_id) 
LEFT JOIN 
    gce_game_team_lineup gtl ON(gtl.player_id = p.id) 
LEFT JOIN 
    gce_game_team gt ON(gt.id = gtl.game_team_id) 
LEFT JOIN 
    gce_game_goal gg ON(gg.player_id = p.id) 
LEFT JOIN 
    gce_game g ON(g.id = gt.game_id) 
GROUP BY p.id 
ORDER BY p.id asc

the above query returns me proper record till total number of games played, i am facing issue fetching the proper records after this, ill appreciate any kind of help on this.

here is the link to sqlfiddle if you want to look at the schema, i have added some test data too.

thank you.

UPDATE :

here are few of the rules to remember.

1. Number of goals scored = total number of goals scored by a player. for example if in gce_game_goal table there are 10 rows which have the value of player_id as 4 it means the player have scored 10 goals and i need to fetch this record for individual player, and likewise if there are 7 rows in which player_id have value of 3 this means player with id 3 have scored 7 goals and likewise.

2. Total number of assist for goals = total number of assist given to a goalie by a player (assist is like a pass in football). i need to calculate total number of assist or pass that was done by a user.
   for each goal there will be two assist, and each assist are players who pass the ball to a golaie. i want to count the number of passes or assist given by a player. for example if in gce_game_goal table there are 8 rows or records that have the value of 3 in either assis1_id or assist2_id column, this means player with id 3 have scored 8 assist in total

.

kindly let me know if you still have any doubts/question, ill try to improve my question

Thanks

Answer 1

The problem that you are facing is caused by aggregating along multiple different dimensions of the data (say by game and by goal). This results in a cross product for each player.

A fairly general solution is to do aggregations in the from clause, along each dimension. Each variable (or perhaps a few variables) comes from a different aggregation:

select u.last_name, u.first_name, p.position, 
       pg.goals, pg.assists, (pg.goals + pg.assists) as TotalPoints
from gce_player p join
     gce_user u
     on p.user_id = u.id left outer join
     (select player_id, SUM(goal) as goals, SUM(assist) as assists
      from ((select player_id, 1 as goal, 0 as assist
             from gce_game_goal
            ) union all
            (select assist1_id, 0 as goal, 1 as assist
             from gce_game_goal
            ) union all
            (select assist2_id, 0 as goal, 1 as assist
             from gce_game_goal
            )
           ) t
      group by player_id
     ) pg
     on pg.player_id = p.id left outer join
     (select gtl.player_id, count(*) as NumTeams
      from gce_game_team_lineup gtl join
           gce_game_team gt
           on gtl.id = gt.team_id
     ) g
     on g.player_id = p.id