LeeZee LeeZee - 6 months ago 12
SQL Question

trying to create column with season-to-date averages of values of another column in same table using MySQL

I'm struggling with determining the MySQL code to create a column (average park_factor) in my table "starting_pitcher_stats" that I'd like to contain the season-to-date in-season average values of the values in another column (park_factor). I'd like this in-season average to be grouped by pitcher and by date.

Ideally, the table would look like this:

pitcher park_fac avg_park_fac date
aased001 94 94 1977-07-31
aased001 100 97 1977-08-06
aased001 108 100.666 1977-08-11
aased001 108 102.5 1977-08-16
aased001 96 101.2 1977-08-21
aased001 108 102.33 1977-08-26
aased001 108 103.14 1977-08-31
aased001 104 103.25 1977-09-05
aased001 108 103.77 1977-09-10
aased001 92 102.6 1977-09-16
aased001 106 102.9 1977-09-22
aased001 108 103.33 1977-09-27


The code I'm using is:

SELECT Starting_Pitcher, full_park_factor, AVG(full_park_factor), Game_Date
FROM starting_pitcher_stats
GROUP BY Starting_Pitcher, Game_Date, Game_Number


...and a sample of the resulting table looks like this:

pitcher park_fac avg_park_fac date
aased001 94 94.0000 1977-07-31
aased001 100 100.0000 1977-08-06
aased001 108 108.0000 1977-08-11
aased001 108 108.0000 1977-08-16
aased001 96 96.0000 1977-08-21
aased001 108 108.0000 1977-08-26
aased001 108 108.0000 1977-08-31
aased001 104 104.0000 1977-09-05
aased001 108 108.0000 1977-09-10
aased001 92 92.0000 1977-09-16
aased001 106 106.0000 1977-09-22
aased001 108 108.0000 1977-09-27


Can someone help please?

Thank you in advance for help with this.
Lee

Answer

You need to join your table on all previous results for the same pitcher from the same table.

I'm not quite sure how you define your season, but assuming it's by calendar year, the following query produces the desired output.

SELECT
    a.Starting_Pitcher, a.full_park_factor,
    AVG(b.full_park_factor), a.Game_Date, a.Game_Number
FROM starting_pitcher_stats a
INNER JOIN starting_pitcher_stats b
    ON a.Starting_Pitcher = b.Starting_Pitcher
    AND (b.Game_Date < a.Game_Date OR
         (b.Game_Date = a.Game_Date AND b.Game_Number <= a.Game_Number))
    AND YEAR(b.Game_Date) = YEAR(a.Game_Date)
GROUP BY a.Starting_Pitcher, a.Game_Date, a.Game_Number;
Comments