I was trying to figure positional adjustments, but got hung up on the command to use. I was basically trying to do something like (Innings/162) * (if pos='c',12.5, if pos='ss',7.5 etc…), if that makes any sense (I'm obviously quite the sql noob)…there is obviously a much better way to do this that eludes me…
Try using a CASE statement… it's meant for instances almost exactly like this, where you have a list of possible values and something different to do depending on what the value is…
select playerid, yearid, CASE WHEN pos = 'C' THEN (innings/162) * 12.5 WHEN pos = 'SS' THEN (innings/162) * 7.5 -- etc ELSE 1 -- use 'ELSE' as the catch-all, to be used on anything that wasn't listed above END as POS_ADJUSTMENT from fielding;
there might be a more elegant way to do it with MySQL variables, but using CASE is a little more generic
Use a nested IF statement
If pos = 'c'
then tot = 12.5
if pos = 'ss'
then tot = 7.5
It will be pretty long, but should work. If is the only method I know, but there might be another method.