Summing fields with criteria

[Chris St. John]

I'm trying to add up fields based on their relative size to another field, but I'm not sure how to do so. Example:

In GAME_ID BOS195004180, BOS scored 10 runs above league average and allowed -5.10 runs above league average. 10 is greater than -5.10, so this gets a "Hitting" value. Then I want to do that for every BOS game that season (home and away) and add up all the Hitting and Pitching values and link it to the teams table. Any ideas on how to do this?

[Chris St. John]

Here is my first shot at it (that isn't working), hopefully this will help make this more clear. I think the use of CASE is correct, but not sure how to actually implement it:

UPDATE games

SET

CASE AwayRsn

WHEN away_score_ct > home_score_ct AND away_hit_dif > away_pit_dif THEN WHit

WHEN away_score_ct > home_score_ct AND away_hit_dif < away_pit_dif THEN WPit

WHEN away_score_ct > home_score_ct AND away_hit_dif = away_pit_dif THEN WBoth

WHEN away_score_ct < home_score_ct AND away_hit_dif > away_pit_dif THEN LPit

WHEN away_score_ct < home_score_ct AND away_hit_dif < away_pit_dif THEN LHit

WHEN away_score_ct < home_score_ct AND away_hit_dif = away_pit_dif THEN LBoth

[Chris St. John]

Okay I think I solved it with this:

UPDATE games

SET AwayRsn = 

(

CASE

WHEN away_score_ct > home_score_ct AND away_hit_dif > away_pit_dif THEN "WHit"

WHEN away_score_ct > home_score_ct AND away_hit_dif < away_pit_dif THEN "WPit"

WHEN away_score_ct > home_score_ct AND away_hit_dif = away_pit_dif THEN "WBoth"

WHEN away_score_ct < home_score_ct AND away_hit_dif > away_pit_dif THEN "LPit"

WHEN away_score_ct < home_score_ct AND away_hit_dif < away_pit_dif THEN "LHit"

WHEN away_score_ct < home_score_ct AND away_hit_dif = away_pit_dif THEN "LBoth"

END

)

So how do I sum all 1950 BOS WHit from AwayRsn and Home Rsn from the games table and join it to the teams table?

[Chris St. John]

Last one, I promise. How do I take this code:

SELECT season, away_team_id, 

COUNT(IF(AwayRsn='WHit',1,NULL)) AS AwayWHit, 

COUNT(IF(AwayRsn='LHit',1,NULL)) AS AwayLHit, 

COUNT(IF(AwayRsn='WPit',1,NULL)) AS AwayWPit, 

COUNT(IF(AwayRsn='LPit',1,NULL)) AS AwayLPit, 

COUNT(IF(AwayRsn='WBoth',1,NULL)) AS AwayWBoth, 

COUNT(IF(AwayRsn='LBoth',1,NULL)) AS AwayLBoth 

FROM games 

GROUP BY season, away_team_id

Which gives me the results I need and change it from just an output to actually going into the teams table in the proper column? I need to join the data based on both season and away_team_id

[Chris St. John]

I created a new column that adds the team and year so I can just do

UPDATE teams

SET teams.AwayG = 

(SELECT COUNT(game_id) FROM games 

WHERE games.yearaway = teams.yearteam)

But it is hanging, taking forever. Not sure where to go from here.

[Max Weinstein]

In Retrosheet, can some kind person explain to me the PITCH_SEQ_TX column (I don't have the table with the Value_CD or the LONGNAME_TX for pitch sequencing).

[James Gentile]

This is a copy from the Retrosheet site somewhere: 

http://uzrillusion2.blogspot.com/2013/04/pitchseqtx.html?m=1


Sent from my iPhone