SELECT NULL as date, person_id, year_id, team_id, player_game_num_season, NULL as team_name_abbr, ts.link as team_name_abbr_link, NULL as opp_name_abbr, os.opp_team_link as opp_name_abbr_link, game_id, sup_games.link, NULL as game_location, (SELECT pts-opp_pts+IFNULL(pts,0)/100 FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id) as game_result_csk, concat(sum(win),'-',sum(loss),if(sum(tie),concat('-',sum(tie)),'')) as game_result, conf_id, NULL AS ranker, SUM(player_game_num_career IS NULL) as player_game_num_career_incomplete, NULL AS player_game_num_career, SUM(team_game_num_season IS NULL) as team_game_num_season_incomplete, NULL AS team_game_num_season, SUM(type IS NULL)+SUM(phase_id IS NULL) as game_type_incomplete, NULL AS game_type, SUM(is_starter IS NULL) as is_starter_incomplete, SUM(is_starter) AS is_starter, SUM(mp IS NULL) as mp_incomplete, SUM(mp) AS mp, SUM(fg IS NULL) as fg_incomplete, SUM(fg) AS fg, SUM(fga IS NULL) as fga_incomplete, SUM(fga) AS fga, IF(SUM(fga), SUM((fga IS NOT NULL) * fg) / SUM(fga), NULL) as fg_pct_csk, SUM(fga IS NULL)+SUM(fg IS NULL) as fg_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga), SUM((fga IS NOT NULL) * fg) / SUM(fga), NULL),3)) AS fg_pct, SUM(fg3 IS NULL) as fg3_incomplete, SUM(fg3) AS fg3, SUM(fg3a IS NULL) as fg3a_incomplete, SUM(fg3a) AS fg3a, IF(SUM(fg3a), SUM((fg3a IS NOT NULL) * fg3) / SUM(fg3a), NULL) as fg3_pct_csk, SUM(fg3a IS NULL)+SUM(fg3 IS NULL) as fg3_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fg3a), SUM((fg3a IS NOT NULL) * fg3) / SUM(fg3a), NULL),3)) AS fg3_pct, SUM(fg3 IS NULL)+SUM(fg IS NULL) as fg2_incomplete, SUM(fg - IFNULL(fg3, 0)) AS fg2, SUM(fga IS NULL)+SUM(fg3a IS NULL) as fg2a_incomplete, SUM(fga - IFNULL(fg3a, 0)) AS fg2a, IF(SUM(fga - IFNULL(fg3a, 0)), SUM(((fga - IFNULL(fg3a, 0)) IS NOT NULL) * (fg - IFNULL(fg3, 0))) / SUM(fga - IFNULL(fg3a, 0)), NULL) as fg2_pct_csk, SUM(fga IS NULL)+SUM(fg IS NULL)+SUM(fg3 IS NULL)+SUM(fg3a IS NULL) as fg2_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga - IFNULL(fg3a, 0)), SUM(((fga - IFNULL(fg3a, 0)) IS NOT NULL) * (fg - IFNULL(fg3, 0))) / SUM(fga - IFNULL(fg3a, 0)), NULL),3)) AS fg2_pct, IF(SUM(fga), SUM(fg * (fga IS NOT NULL) + 0.5 * IFNULL(fg3, 0)) / SUM(fga), NULL) as efg_pct_csk, SUM(fga IS NULL)+SUM(fg IS NULL)+SUM(fg3 IS NULL) as efg_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga), SUM(fg * (fga IS NOT NULL) + 0.5 * IFNULL(fg3, 0)) / SUM(fga), NULL),3)) AS efg_pct, SUM(ft IS NULL) as ft_incomplete, SUM(ft) AS ft, SUM(fta IS NULL) as fta_incomplete, SUM(fta) AS fta, IF(SUM(fta), SUM(ft * (fta IS NOT NULL)) / SUM(fta), NULL) as ft_pct_csk, SUM(fta IS NULL)+SUM(ft IS NULL) as ft_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fta), SUM(ft * (fta IS NOT NULL)) / SUM(fta), NULL),3)) AS ft_pct, SUM(orb IS NULL) as orb_incomplete, SUM(orb) AS orb, SUM(orb IS NULL)+SUM(trb IS NULL) as drb_incomplete, SUM(trb - orb) AS drb, SUM(trb IS NULL) as trb_incomplete, SUM(trb) AS trb, SUM(ast IS NULL) as ast_incomplete, SUM(ast) AS ast, SUM(stl IS NULL) as stl_incomplete, SUM(stl) AS stl, SUM(blk IS NULL) as blk_incomplete, SUM(blk) AS blk, SUM(tov IS NULL) as tov_incomplete, SUM(tov) AS tov, SUM(pf IS NULL) as pf_incomplete, SUM(pf) AS pf, SUM(pts IS NULL) as pts_incomplete, SUM(pts) AS pts, SUM(pts + 0.4 * fg - 0.7 * fga - 0.4 * (fta - ft) + 0.7 * orb + 0.3 * (trb - orb) + stl + 0.7 * ast + 0.7 * blk - 0.4 * pf - tov) / COUNT(*) as game_score_csk, SUM(tov IS NULL)+SUM(pts IS NULL)+SUM(orb IS NULL)+SUM(blk IS NULL)+SUM(stl IS NULL)+SUM(pf IS NULL)+SUM(ast IS NULL)+SUM(trb IS NULL)+SUM(fta IS NULL)+SUM(fg IS NULL)+SUM(ft IS NULL)+SUM(fga IS NULL) as game_score_incomplete, ROUND(SUM(pts + 0.4 * fg - 0.7 * fga - 0.4 * (fta - ft) + 0.7 * orb + 0.3 * (trb - orb) + stl + 0.7 * ast + 0.7 * blk - 0.4 * pf - tov) / COUNT(*),1) AS game_score
FROM sup_player_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, part_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie
FROM sup_team_games
   ) as tg USING (game_id, team_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  JOIN (SELECT team_id, year_id, name_short, name, link
FROM sup_team_seasons
  
GROUP BY team_id, year_id ) as ts USING (team_id, year_id)
  LEFT JOIN (SELECT team_id as opp_team_id, link as opp_team_link, year_id, name_abbr, name_short
FROM sup_team_seasons
  
GROUP BY team_id, year_id ) as os USING (opp_team_id, year_id)
  JOIN (SELECT team_id, comp_id, year_id, phase_id, name_short, conf_id, link
FROM sup_team_seasons
   ) as sts USING (team_id, comp_id, year_id, phase_id)
  LEFT JOIN (SELECT parent_conf_id, conf_id, conf_name, conf_abbr, link as conf_abbr_link, comp_id, year_id
FROM sup_conference_seasons
   ) as team_conf USING (conf_id, comp_id, year_id)
 WHERE person_id = ?
   AND year_id = ? 
GROUP BY person_id
ORDER BY `date` asc