Players' statistics are stored in the database by each team that they played for in each year (each "stint"). The following views can be used to work with a player's full year stats. This does not separate by league (i.e., AL stats vs. NL stats), though that can be achieved with a minor tweak.
MySQL
Batting
Use the following code to get a batter's totals for the year:
create view bat_year_total as select playerid, yearid, sum(g) as G, sum(g_batting) as G_batting, sum(ab) as AB, sum(r) as R, sum(h) as H, sum(2b) as 2B, sum(3B) as 3B, sum(HR) as HR, sum(RBI) as RBI, sum(sb) as SB, sum(CS) as CS, sum(bb) as BB, sum(so) as SO, sum(IBB) as IBB, sum(hbp) as HBP, sum(SH) as SH, sum(SF) as SF, sum(GIDP) as GIDP, sum(g_old) as G_old from batting group by playerid, yearid ;
Use the following code to also get a batter's averages for the year (this code relies on the previous view being created):
create view bat_year_total_avg as select b.playerid AS playerid, b.yearid AS yearid, b.g_batting AS g_batting, b.ab as AB, b.r as R, b.h as H, b.2b as 2B, b.3b as 3B, b.hr as HR, b.rbi as RBI, b.sb as SB, b.cs as CS, b.bb as BB, b.so AS SO, b.ibb as IBB, b.hbp AS HBP, b.sh as SH, b.sf AS SF, b.gidp AS GIDP, b.g_old AS G_old, round(b.h / b.ab, 3) as AVG, round( (b.h + b.bb + b.hbp) / (b.ab + b.bb + b.hbp + b.sf), 3) as OBP, round( (b.h + b.2b + 2*b.3b + 3*b.hr) / b.ab, 3) as SLG, round((b.h + b.bb + b.hbp) / (b.ab + b.bb + b.hbp + b.sf) + (b.h + b.2b + 2*b.3b + 3*b.hr) / b.ab, 3) as OPS, round( (b.h - b.hr) / (b.ab - b.SO - b.HR + b.SF), 3) as BABIP from bat_year_total b ;
Pitching
Use the following code to get a pitcher's totals for the year:
create view pitch_year_total as select playerid, yearid, sum(w) as W, sum(L) as L, sum(G) as G, sum(gs) as GS, sum(cg) as CG, sum(sho) as SHO, sum(sv) as SV, sum(ipouts) as IPouts, sum(H) as H, sum(ER) as ER, sum(HR) as HR, sum(BB) as BB, sum(SO) as SO, sum(IBB) as IBB, sum(wp) as WP, sum(HBP) as HBP, sum(BK) as BK, sum(BFP) as BFP, sum(GF) as GF, sum(R) as R from pitching group by playerid, yearid ;
Use the following code to also get a pitcher's averages for the year (this code relies on the previous view being created):
create view pitch_year_total_avg as select p.playerid AS playerid, p.yearid AS yearid, p.W AS W, p.L AS L, p.G AS G, p.GS AS GS, p.CG AS CG, p.SHO AS SHO, p.SV AS SV, p.IPouts AS IPouts, p.H AS H, p.ER AS ER, p.HR AS HR, p.BB AS BB, p.SO AS SO, p.IBB AS IBB, p.WP AS WP, p.HBP AS HBP, p.BK AS BK, p.BFP AS BFP, p.GF AS GF, p.R AS R, round(((p.ER * 9) / (p.IPouts / 3)), 2) AS ERA, round(((p.BB + p.H) / (p.IPouts / 3)), 3) AS WHIP from pitch_year_total p ;
Fielding
Use the following code to get a fielder's totals for the year:
create view field_year_total as select playerid, yearid, pos, sum(g) as G, sum(gs) as GS, sum(InnOuts) as InnOuts, sum(PO) as PO, sum(A) as A, sum(E) as E, sum(DP) as DP, sum(pb) as PB, sum(wp) as WP, sum(SB) as SB, sum(CS) as CS from fielding group by playerid, yearid, pos ;
Use the following code to also get a fielder's averages for the year (this code relies on the previous view being created):
create view field_year_total_avg as select f.playerid, f.yearid, f.pos, f.g as G, f.gs as GS, f.innouts AS InnOuts, f.po as PO, f.a as A, f.e as E, f.dp as DP, f.pb as PB, f.wp as WP, f.sb as SB, f.cs as CS, round( (f.PO + f.A) / (f.PO + f.A + f.E) , 3) as FAVG from field_year_total f ;