SELECT

        OUTS_CT AS START_OUTS

        , START_BASES_CD AS START_BASES

        , if(INN_CT>9,9,INN_CT) as inn

    , IF(BAT_HOME_ID=1,'b','t') AS half

        , HOME_SCORE_CT-AWAY_SCORE_CT AS diff

        , COUNT(*) AS PA

# INTO OUTFILE '/tmp/game_state_frequency.csv'

# move file from tmp to working directory

FROM

(SELECT * FROM pbp.20xx

WHERE

    BAT_EVENT_FL = 'T'

        AND substr(game_ID,4,4) >= 1993

        AND substr(game_ID,4,4) <= 2010

# the following union is because I have 20th and 21st century in separate tables

# you can delete the following if you only have one table

UNION ALL

SELECT * FROM pbp.19xx

WHERE

    BAT_EVENT_FL = 'T'

        AND substr(game_ID,4,4) >= 1993

        AND substr(game_ID,4,4) <= 2010)T

WHERE HOME_SCORE_CT-AWAY_SCORE_CT between -16 and 16

GROUP BY

        OUTS_CT

        , START_BASES_CD

        , if(INN_CT>9,9,INN_CT)

        , BAT_HOME_ID

        , HOME_SCORE_CT-AWAY_SCORE_CT

;