CREATE TABLE RidgebaseSBA AS SELECT a.GAME_ID,
    CASE
        WHEN
            (a.RUN1_SB_FL OR a.RUN1_CS_FL) = 'T'
                AND a.start_bases_cd = 1
                OR a.start_bases_cd = 5
        THEN
            CONCAT(a.pit_ID,
                    '+',
                    a.POS2_FLD_ID,
                    '+',
                    a.BASE1_RUN_ID)
        WHEN
            (a.RUN2_SB_FL OR a.RUN2_CS_FL) = 'T'
                AND a.start_bases_cd = 2
        THEN
            CONCAT(a.pit_ID,
                    '+',
                    a.POS2_FLD_ID,
                    '+',
                    a.BASE2_RUN_ID)
    END AS battery,
    a.pit_id AS pitcher,
    a.POS2_FLD_ID AS catcher,
    CASE
        WHEN
            (a.RUN1_SB_FL OR a.RUN1_CS_FL) = 'T'
                AND a.start_bases_cd = 1
                OR a.start_bases_cd = 5
        THEN
            a.BASE1_RUN_ID
        WHEN
            (a.RUN2_CS_FL OR a.RUN2_SB_FL) = 'T'
                AND a.start_bases_cd = 2
        THEN
            a.BASE2_RUN_ID
    END AS baserunner,
    CASE
        WHEN a.OUTS_CT = 0 THEN 1
        ELSE 0
    END AS OUTS_0,
    CASE
        WHEN a.OUTS_CT = 1 THEN 1
        ELSE 0
    END AS OUTS_1,
    CASE
        WHEN a.OUTS_CT = 2 THEN 1
        ELSE 0
    END AS OUTS_2,
    CASE
        WHEN a.START_BASES_CD = 1 THEN 1
        ELSE 0
    END AS BS1__,
    CASE
        WHEN a.START_BASES_CD = 2 THEN 1
        ELSE 0
    END AS BS_2_,
    CASE
        WHEN a.START_BASES_CD = 5 THEN 1
        ELSE 0
    END AS BS1_3,
    SUM(IF(a.EVENT_CD = 6, 1, 0)) AS CS,
    SUM(IF(a.EVENT_CD = 4, 1, 0)) AS SB,
    SUM(IF(a.EVENT_CD = 6, 1, 0)) + SUM(IF(a.EVENT_CD = 4, 1, 0)) AS SBA,
    SUM(a.EVENT_OUTS_CT) / 3 AS IP,
    CASE WHEN a.WP_FL = "T" THEN 1 ELSE 0 END AS WP,
    CASE WHEN a.PB_FL = "T" THEN 1 ELSE 0 END AS PB,
    CASE WHEN a.EVENT_CD = 8 THEN 1 ELSE 0 END AS PK,
    CASE
        WHEN BAT_HOME_ID = 1 THEN a.HOME_SCORE_CT - a.AWAY_SCORE_CT
        ELSE a.AWAY_SCORE_CT - a.HOME_SCORE_CT
    END AS scoremargin,
    a.INN_CT,
    CASE
        WHEN a.PIT_HAND_CD = 'L' THEN 1
        ELSE 0
    END AS P_hand_L,
    CASE
        WHEN a.PIT_HAND_CD = 'R' THEN 1
        ELSE 0
    END AS P_hand_R,
    CASE
        WHEN a.BAT_HAND_CD = 'L' THEN 1
        ELSE 0
    END AS B_hand_L,
    CASE
        WHEN a.BAT_HAND_CD = 'R' THEN 1
        ELSE 0
    END AS B_hand_R FROM
    events a
WHERE
    a.start_bases_cd = 1
        OR a.start_bases_cd = 2
        OR a.start_bases_cd = 5
GROUP BY a.game_id , a.pit_id , a.pos2_fld_id  , a.inn_ct,  CASE
        WHEN
            (a.RUN1_SB_FL OR a.RUN1_CS_FL) = 'T'
                AND a.start_bases_cd = 1
                OR a.start_bases_cd = 5
        THEN
            a.BASE1_RUN_ID
        WHEN
            (a.RUN2_CS_FL OR a.RUN2_SB_FL) = 'T'
                AND a.start_bases_cd = 2
        THEN
            a.BASE2_RUN_ID
    END;