Team Pitching Split Finder

/* mysql8 */
SELECT group_id, subgroup_id, split_name,
  "pitch" as type_of_split,
  IF(team_ID NOT LIKE '%TM' AND team_ID NOT LIKE 'TOT',CONCAT('/teams/',team_ID,'/',year_ID,'.shtml'),'') AS team_link,
  mt.team_ID AS team,
  mt.name AS team_title,
  mt.lg_ID AS lg_id,
  A.year_game,
  ROUND(IPouts/3,1) AS IP,
  IPouts,
  W, L, S as SV, G, GS, GF, CG, SHO, 
  ROUND(IF((W + L) = 0, NULL, ROUND(W / (W + L),3)),3) AS win_loss_perc,
  IF(IPouts = 0, IF(ER=0, NULL, 'inf'), ROUND(27 * ER /IPouts ,2)) as earned_run_avg,
  ER, R, H, BB, SO, HR, BF,
  WP, BK, 
  IF(IPouts = 0, NULL, ROUND(27 * BB / IPouts,2))  as BBp9,
  IF(IPouts = 0, NULL, ROUND(27 * HR / IPouts,2))  as HRp9,
  IF(IPouts = 0, NULL, ROUND(27 * SO / IPouts,2))  as SOp9,
  IF(IPouts = 0, NULL, ROUND(27 * H  / IPouts,2))   as Hp9,
  ROUND(IF(IPouts = 0, NULL, ROUND(3 * (H + BB) / IPouts,10)),3)  as whip,
  ROUND(IF(BB = 0, IF(SO=0, NULL, 1e20), ROUND(SO / BB, 10)),2) as sopbb,
  ROUND(IF(AB-HR-IFNULL(SO,0)+IFNULL(SF,0) = 0, NULL, ROUND((H-HR)/(AB-HR-IFNULL(SO,0)+IFNULL(SF,0)),10)),3) AS batting_avg_bip,
  (AB-HR-IFNULL(SO,0)+IFNULL(SF,0))            AS bip,
  ROUND(IF(AB = 0, NULL, ROUND(H  / AB,10)),3)     AS batting_avg,
  ROUND(IF((AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)) = 0, NULL, ROUND((H + IFNULL(BB,0) + IFNULL(HBP,0)) / (AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)),10)),3)     AS onbase_perc,
  ROUND(IF(AB = 0, NULL, ROUND((H + 2B + (2 * 3B) + (3 * HR)) / AB, 10)),3)   AS slugging_perc,
  sOPS,
  tOPS,
  ROUND(IF( (HBP is NULL) or  (SH is NULL) or  (SF is NULL) or  (CS is NULL) or  (GIDP is NULL) or  (IBB is NULL), IF( (CS is NULL), (H + BB) * ((H+2B+(2*3B)+(3*HR))) / (AB + BB),  (H + BB - CS) * ( H + 2B + 2*3B + 3*HR + 0.55*SB ) / (AB + BB)  ),  (H + BB - CS + ifnull(HBP,0) - ifnull(GIDP,0)) * ( H + 2B + 2*3B + 3*HR + .26*(BB - ifnull(IBB,BB) + ifnull(HBP,0)) + 0.52*(SB + ifnull(SH,0) + ifnull(SF,0) ) ) / (AB + BB + ifnull(HBP + SH + SF,0))  ),1)      as RC_split,
  AB, H - (2B + 3B + HR) as 1B, 2B, 3B, 
  (H + 2B + (2 * 3B) + (3 * HR)) AS TB, 
  IBB, SB, CS, HBP, SH, SF, GIDP, ROE,
  ROUND(IF((AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)) = 0, NULL, ROUND((H + IFNULL(BB,0) + IFNULL(HBP,0)) / (AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)),10)) + IF(AB = 0, NULL, ROUND((H + 2B + (2 * 3B) + (3 * HR)) / AB, 10)),3) as onbase_plus_slugging,
  (W + L),
  G-GS,
  (H + IFNULL(BB,0) + IFNULL(HBP,0)) + IFNULL(ROE,0),
  IF(AB = 0, NULL, ROUND(((H + 2B + (2 * 3B) + (3 * HR)) - H) / AB, 10)),
  (2B + 3B + HR),
  pickoffs,
  (H + IFNULL(BB,0) + IFNULL(HBP,0)),
  IF(incomplete=1,'I','') AS incomplete_split
FROM tpsplit AS A
  LEFT JOIN majors_team_view mt on mt.team_ID=A.pitching_team_id AND mt.year_ID=A.year_game  
WHERE 1 > 0 AND A.year_game >= 2025 AND A.year_game <= 2025 AND A.pitching_team_id!="TOT" AND  group_id='locat' AND  subgroup_id='hmvis' AND  split_name='Home'
ORDER BY SO DESC, team ASC, A.year_game DESC
LIMIT 0, 300


$VAR1 = [];
/* mysql8 */
SELECT CONCAT(group_id,':',subgroup_id,':',split_name) as id, MAX(year_game) as max_year
FROM tpsplit
GROUP BY group_id, subgroup_id, split_name


$VAR1 = [];
/* mysql8 */
SELECT gid, sid, snm, split_order.spo, gid as group_id, sid AS subgroup_id, snm AS split_name
FROM psplit_order as split_order
GROUP BY gid,sid,snm
ORDER BY split_order.spo


$VAR1 = [];
/* mysql8 */
SELECT gid, sid, min(split_order.spo) AS first
FROM psplit_order as split_order
WHERE 1>0 
GROUP BY gid, sid
ORDER BY first ASC


$VAR1 = [];