Okay, so next up are individual pitching career marks. I thought this would be harder than it wound up being, and had an idea that I was going to break things down a bit and try and demonstrate how you can develop around the limitations of SQL (the big one being, no real way to debug). However, I mathed the field I needed to math out (innings pitched) without having to break stuff down. I have an idea for breaking stuff apart next, however.
Anyway, this is Braves pitcher George House, who will at least be up for the NL Cy Young this season, along with the SQL:
Code:
ATL 1967 5 5 0.500 1 3.67 25 5 1 0 12 73.2 296 67 30 30 6 2 4 2 25 3 63 3 0 8.2 0.7 3.1 7.7
ATL 1968 16 7 0.696 0 1.99 31 31 5 0 0 231.0 899 178 62 51 18 11 8 8 49 5 184 9 0 6.9 0.7 1.9 7.2
ATL 1969 21 8 0.724 0 2.70 34 34 11 3 0 256.2 1038 251 87 77 17 15 8 15 49 5 175 3 0 8.8 0.6 1.7 6.1
Totals 42 20 0.677 1 2.53 90 70 17 3 12 561.1 2233 496 179 158 41 28 20 25 123 13 422 15 0 8.0 0.7 2.0 6.8
Code:
SET @player_id = 14787;
SELECT
tm.abbr,
pit.year,
pit.w,
pit.l,
round(pit.w / (pit.w + pit.l), 3) as Pct,
pit.s as Sv,
round((pit.er * 9) / (pit.ip + (pit.ipf / 3)), 2) as era,
pit.g,
pit.gs,
pit.cg,
pit.sho,
pit.gf,
concat(pit.ip, ".", pit.ipf) as ip,
(pit.ab + pit.bb + pit.hp) as bfp,
pit.ha as h,
pit.r,
pit.er,
pit.hra as hr,
pit.sh,
pit.sf,
pit.hp,
pit.bb,
pit.iw as ibb,
pit.k as so,
pit.wp,
pit.bk,
round(ha / (ip + ipf / 3) * 9,1) as Hper9,
round(hra / (ip + ipf / 3) * 9,1) as HRper9,
round(bb / (ip + ipf / 3) * 9,1) as BBer9,
round(k / (ip + ipf / 3) * 9,1) as Kper9
FROM `modern-ish-baseball`.players_career_pitching_stats pit
LEFT JOIN players plr
on plr.player_id = pit.player_id
LEFT JOIN teams tm
on pit.team_id = tm.team_id
WHERE pit.player_id = @player_id
and split_id = 1
and pit.league_id = 100
UNION
SELECT
'Totals',
'',
sum(pit.w),
sum(pit.l),
round(sum(pit.w) / (sum(pit.w) + sum(pit.l)), 3),
sum(pit.s),
round(sum(pit.er) / (sum(pit.ip) + sum(pit.ipf) / 3) * 9, 2),
sum(pit.g),
sum(pit.gs),
sum(pit.cg),
sum(pit.sho),
sum(pit.gf),
concat(sum(ip) + floor(sum(ipf) / 3),'.', sum(ipf) % 3),
sum(pit.ab) + sum(pit.bb) + sum(pit.hp),
sum(pit.ha),
sum(pit.r),
sum(pit.er),
sum(pit.hra),
sum(pit.sh),
sum(pit.sf),
sum(pit.hp),
sum(pit.bb),
sum(pit.iw),
sum(pit.k),
sum(pit.wp),
sum(pit.bk),
round(sum(pit.ha) / (sum(pit.ip) + sum(pit.ipf) / 3) * 9,1),
round(sum(pit.hra) / (sum(pit.ip) + sum(pit.ipf) / 3) * 9,1),
round(sum(pit.bb) / (sum(pit.ip) + sum(pit.ipf) / 3) * 9,1),
round(sum(pit.k) / (sum(pit.ip) + sum(pit.ipf) / 3) * 9,1)
FROM `modern-ish-baseball`.players_career_pitching_stats pit
LEFT JOIN players plr
on plr.player_id = pit.player_id
LEFT JOIN teams tm
on pit.team_id = tm.team_id
WHERE pit.player_id = @player_id
and split_id = 1
and pit.league_id = 100
group by pit.player_id
The totals lines are basically figured the same as for hitters. By the way, if you wanted to add a "career highs" line, you can basically copy and paste the second half - everything from "union" on down - and replace sum() with max().
For innings pitched, if you'll remember from the team report we have two numbers: IP and IPF (fractions of an inning), and the way we lay out baseball IP is {inning}.{fraction}. This is easy enough to break down for one season but what about when you have a guy who's pitched 450 innings and 5 inning-fractions?
The math above show how but just to break it down....
Innings = Innings (duh) + fractions divided by 3, rounded down. In mySQL the floor() function takes a decimal number and just lops off everything after the decimal point (i.e. rounding down).
Fractions = the remainder of fractions divided by 3. In coding and I don't know, maybe in math too, we call the remainder the modulus and use the % key to figure it out. Also very useful for figuring average-and-over for you 19th century players!