Recent Forum Posts
From categories:
page »

…two years later

I have just discovered this and others.

Compiled chadwick tools on my mac and built game, event and sub (whatever that is) files to load into mysql. I will use the data to play with visualization tools like tableau and

I would love to exchange info.

I'm constantly referencing this site for my often aimless but always engaging queries. Are there any other silent participants out there? I would love to get some conversation going.

Team totals
obarronobarron 04 Mar 2010 03:46
in discussion Databases / MySQL » Team totals

I'm a total newbie at sabermetrics, so I am just playing around with a mysql database with the Baseball databank installed. I was reading something about payrolls and wanted to see payrolls for the last 10 years or so. Couldn't find it on google, so I fired up MAMP/Sequel Pro and tried to write a query. I wanted to see payroll for all MLB teams for 2008 (later I'd want to combine 2000-2009 or so). Here's my code:

sum(salary) AS PAYROLL
FROM salaries
HAVING yearID=2008

Didn't work…no error, but no output. What am I doing wrong? And how would I get 2000-2009 figures?


Team totals by obarronobarron, 04 Mar 2010 03:46

I ran Colin's GameDay fielder code after loading RecentGames.bat into my gameday database from BBOS just to see what it did. Then, I loaded AllAvailableGames.bat into my gameday database to get all the BBOS games. When I look in gameday.event_player table ( that Colin's code created), there are only games from the RecentGames load. What do I need to do to have it include all the new games in my database?

Also, how would I retrieve batted ball x,y locations prior to the Pitch F/X GameDay XML data ~2006?

could anyone help me parse the pitch sequence strings? i'd like to remove all pickoff throws from the strings, and my best effort was gonna be to use the replace function to delete them essentially, but i was wondering if someone knows a way to pull the characters that represent pitches out of the string and into a new column (is that possible?) any ideas, as always, are greatly appreciated, have an awesome day!

oh man, I was really hoping you might write a tutorial exactly like this at! thank you so much!

There's something from SABRMatt on the RetroSQL list. I haven't looked at it yet. Retrosheet's games table has limited weather information as well.

Re: Weather Data by Colin WyersColin Wyers, 08 Mar 2009 16:18

Hello! I was wondering if anyone could suggest some simple ways to get a txt file created using bevent into a database…I used bevent to print the pitch sequence and pitcher for each at bat…I was gonna import it into excel and use the len function to count the length of each pitch sequence (which would give me a list of pitcher and number pitches for each at bat) and then load it into my database and use sqlyog to sum pitches by pitcher id…I guess I am wondering if there is a way to just import the txt as a table into sql, and sum the pitches by pitcher id? Hope I explained my question almost clearly…sorry otherwise!

Weather Data
xeifrankxeifrank 04 Mar 2009 00:35
in discussion General / General Discussion » Weather Data

Are there any databases available that have weather data for each game?
Temperate and wind vector.
vr, Xei

Weather Data by xeifrankxeifrank, 04 Mar 2009 00:35

right on, thank you very much for the clarification! aren't there outfield positional breakdowns for the retrosheet era? Is the the data from 1956-1995 too unreliable to be used?

I probably didn't write that as clearly as I could/should have. Prior to 1996, fielding numbers in the Databank are only listed as OF; after 1995, they're broken down into the three OF positions as well. For those years where we have CF, LF and RF breakdowns, we're uninterested in the OF data, so that was discarded.

I have another (probably pretty lame) question about the from statement in the primary position query

"FROM (SELECT * from fielding
WHERE IF(yearID>1995 AND POS = "OF",1,0) != 1 ORDER BY G Desc) f"

I can't really figure out whats going on there.

The CASE is what I'd use for a one-off query. The other option is to set up a table with the positional adjustments, and then to use a JOIN statement.

I have a new CSV I want to add to an existing database (sql database created with the help of Colin's articles), what is the easiest way to do this? The first row of data is the columns headings.

primary pos data from:

hey lar, thanks a ton! the case statement helped me do exactly what i was trying to do!

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…

for example

select playerid, yearid,
        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
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.

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…

page »
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License