Player Season Stats Finder - FBref

--------------------------------------

main

Page Setup Time: 0.00215 seconds

SELECT name_display, name_display_csk, sup_players.link as name_display_link, IFNULL(age, "") as age, IF(COUNT(DISTINCT team_id) > 1, CONCAT(COUNT(DISTINCT team_id), ' Teams'), GROUP_CONCAT(DISTINCT CONCAT('<a target="_blank" href="DOMAIN', ts.link, '">', IFNULL(ts.name_short, ts.name), '</a>'))) as teams_played_for, year_id as year_id_csk, year_id as year_id, is_complete_comp_season, 
  CASE position
  WHEN 'FW'     THEN 4
  WHEN 'FW,DF'  THEN 3.3
  WHEN 'FW,MF'  THEN 3.5
  WHEN 'MF,FW'  THEN 3.4
  WHEN 'MF'     THEN 3
  WHEN 'MF,DF'  THEN 2.4
  WHEN 'DF,MF'  THEN 2.3
  WHEN 'DF'     THEN 2
  WHEN 'DF,FW'  THEN 2.5
  WHEN 'GK'     THEN 1
  ELSE               0
  END
  as pos_csk, position as pos, IF(COUNT(DISTINCT sup_comps.comp_id) > 1, CONCAT(COUNT(DISTINCT sup_comps.comp_id), ' Comps'), GROUP_CONCAT(DISTINCT sup_comps.name)) as comp_name_abbr_csk, IF(COUNT(DISTINCT sup_comps.comp_id) > 1, CONCAT(COUNT(DISTINCT sup_comps.comp_id), ' Comps'), GROUP_CONCAT(DISTINCT sup_comps.name)) as comp_name_abbr, person_id, comp_id, phase_id, mod_nationality.fifa_id as nationality_csk, mod_nationality.fifa_id as nationality, is_big5, SUM(games IS NULL) as games_incomplete, SUM(games) AS games, SUM(games > 0 AND minutes IS NULL) as minutes_incomplete, SUM(minutes) AS minutes, SUM(minutes) / 90 as minutes_90s_csk, SUM(games > 0 AND minutes IS NULL) as minutes_90s_incomplete, ROUND(SUM(minutes) / 90,1) AS minutes_90s, SUM(games > 0 AND games_sub IS NULL) as games_starts_incomplete, SUM(games-games_sub) AS games_starts, SUM(games > 0 AND games_sub IS NULL) as games_subs_incomplete, SUM(games_sub) AS games_subs, SUM(games > 0 AND unused_sub IS NULL) as unused_subs_incomplete, SUM(unused_sub) AS unused_subs, SUM(goals IS NULL) as goals_incomplete, SUM(goals) AS goals, SUM(assists IS NULL) as assists_incomplete, SUM(assists) AS assists, SUM(goals IS NULL)+SUM(assists IS NULL) as goals_assists_incomplete, SUM(goals + assists) AS goals_assists, SUM(goals IS NULL)+SUM(pens_made IS NULL) as goals_pens_incomplete, SUM(goals - pens_made) AS goals_pens, SUM(pens_made IS NULL) as pens_made_incomplete, SUM(pens_made) AS pens_made, SUM(pens_att IS NULL) as pens_att_incomplete, SUM(pens_att) AS pens_att, SUM(games > 0 AND (pens_att IS NULL OR pens_made IS NULL)) as pens_missed_incomplete, SUM((pens_att IS NOT NULL AND pens_made IS NOT NULL)*pens_att) - SUM((pens_att IS NOT NULL AND pens_made IS NOT NULL)*pens_made) AS pens_missed, 0 as nationality_flag_incomplete, nationality_flag, 0 as nationality_link_incomplete, CONCAT('/en/country/', fifa_members.fifa_id, '/', REPLACE(fifa_members.name, ' ', '-'), '-Football') AS nationality_link, SUM(comp_flag IS NULL)+SUM(sup_comps.comp_id IS NULL) as comp_name_abbr_flag_incomplete, IF(COUNT(DISTINCT sup_comps.comp_id) > 1, '', comp_flag) AS comp_name_abbr_flag
FROM sup_player_team_seasons as st
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_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_abbr, link, phase_id, comp_id, name_short, name, minutes AS ts_minutes FROM sup_team_seasons) as ts USING (team_id, year_id, phase_id, comp_id)
  JOIN sup_comps USING (comp_id)
  JOIN sup_approved_comp_seasons USING (comp_id, year_id)
  LEFT JOIN mod_nationality USING (person_id)
  LEFT JOIN fifa_members USING (fifa_id)
  LEFT JOIN (SELECT person_id, fifa_id, flag AS nationality_flag, name FROM mod_nationality
  JOIN fifa_members USING (fifa_id)) as nationality_flags USING (person_id)
  JOIN sup_teams USING (team_id)
  LEFT JOIN (SELECT org_id AS governing_org, fifa_id, flag AS comp_flag FROM orgs
  JOIN fifa_members USING (fifa_id)) as comp_flags USING (governing_org)
WHERE year_id >= '1988-1989'
  AND phase_id=?
  AND sup_comps.gender='m'
  AND year_id>=?
  AND year_id<=?
  AND sup_comps.age_level='senior'
  AND is_big5=1 
GROUP BY person_id, year_id
ORDER BY goals DESC, person_id LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          '0',
          '2025-2026',
          '2025-2026'
        ];

Query Time: 0.14 seconds
Table Build Time: 0.06 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'force_min_year' => 1,
          'phase_id' => '0',
          'comp_gender' => 'm',
          'year_min' => '2025-2026',
          'per90min_val' => 5,
          'year_max' => '2025-2026',
          'match' => 'player_season',
          'height_type' => 'height_meters',
          'weight_type' => 'kgs',
          'comp_type' => 'b5',
          'yob_comp' => 'gt',
          'order_by' => 'goals'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'force_min_year' => 1,
          'year_max' => '2025-2026',
          'year_min' => '2025-2026',
          'comp_type' => 'b5',
          'phase_id' => '0'
        };