Full Year Statistics

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 ;