SQL/Python - Multiple Selects and calculating within SQL

Question

I have a database of scraped play by play data from the MLB gameday XML feed.

I want to be able to pull a list of batting averages and other statistics but I am stuck.

I have an events table (each at-bat) which is setup as following

  `event_id` int(15) NOT NULL AUTO_INCREMENT,
  `game_id` int(8) NOT NULL,
  `batter` int(8) NOT NULL,
  `pitcher` int(8) NOT NULL,
  `inning` int(3) NOT NULL,
  `event` varchar(25) NOT NULL,
  `outs` int(1) NOT NULL,
  `rbi` int(1) NOT NULL,
  `home_runs` int(3) NOT NULL,
  `away_runs` int(3) NOT NULL,
  `batting_team` int(3) NOT NULL,
  `b1` int(6) NOT NULL,
  `b2` int(6) NOT NULL,
  `b3` int(6) NOT NULL,
  `pitch_detail` varchar(200) NOT NULL,
  PRIMARY KEY (`event_id`)

And a players table that simply holds a record of player_id, first_name, last_name, and team_id

I am pretty limited in my SQL and have got only as far as

SELECT players.first_name, players.last_name, count(events.event), events.event
FROM mlb.events
JOIN mlb.players
ON mlb.players.player_id = mlb.events.batter
WHERE team_id = %s
GROUP BY events.batter, events.event
ORDER BY mlb.events.batter, events.event

This returns a list as follows

('Henry', 'Blanco', 3L, 'Flyout')
('Henry', 'Blanco', 4L, 'Groundout')
('Henry', 'Blanco', 4L, 'Single')
('Henry', 'Blanco', 5L, 'Strikeout')

For each player.

I could extract this into python and make it usable but I figure there must be SQL that I can write that can do this for me. I need to calculate hits/(outs+hits) to give me his average. What I want out is as follows.

('Henry', 'Blanco', 0.285)

For ease of example lets just say the only outs are Flyout, Groundout, Strikeout and the hits are Single, Double, Triple and Home Run.

Any help or direction greatly appreciated.

Sorry. it would be something along the lines of hits/(hits+outs) where hits = Single, Double, Triple, Home Run and outs = Flyout, Groundout, Strikeout. Is this sort of maths possible in SQL or will I need to bring it back into Python and then work on it? Thanks! — user2073606
– user2073606, Commented Mar 26, 2014 at 22:15
I'm still confused on the 0.285. Wouldn't this example yield 4/(4+12) = 0.25? — Jeremy Stein
– Jeremy Stein, Commented Mar 26, 2014 at 23:43
Oh, haha, that was just an example of a value (just to show it would be a float between 0 and 1. Yes for this example it would be 0.25. Sorry for the confusion. — user2073606
– user2073606, Commented Mar 27, 2014 at 9:13

Jeremy Stein · Accepted Answer · 2014-03-27 17:33:09Z

1

I would suggest creating another table with events and whether they are runs or outs. Then you could join the tables.

But even without doing that, you could write the query this way:

SELECT players.first_name, players.last_name,
  SUM(CASE events.event
        WHEN 'Single' THEN 1
        WHEN 'Double' THEN 1
        WHEN 'Triple' THEN 1
        WHEN 'Home Run' THEN 1
        ELSE 0
      END)
  / COUNT(events.event) AS avg
FROM mlb.events
JOIN mlb.players
ON mlb.players.player_id = mlb.events.batter
WHERE team_id = %s
GROUP BY events.batter
ORDER BY mlb.events.batter

answered Mar 27, 2014 at 17:33

Jeremy Stein

19.8k18 gold badges71 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2073606 Over a year ago

Hi, thanks for that. I see one potential problem that I haven't explained. Is there an easy way of excluding cases. For instance, if a player walks to first base (signified at the moment by the string 'Walk') then it is just isn't included in the average.

Jeremy Stein Over a year ago

Yep, just make the denominator a CASE statement. (Or, again, use a join.)

Collectives™ on Stack Overflow

SQL/Python - Multiple Selects and calculating within SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related