ATV360 ATV360 - 2 years ago 72
SQL Question

Get a list of rows showing the average found for each year MYSQL

so I've searched and found a lot of questions similar to mine such as
MYSQL - get a row for each year, with total sum for each month but I was looking for a different way. I'm using the Lahman database and finding the top ten players by WAR each year. I'm then averaging those players ages to give the average age for that season. I'm trying to do this for every year but I'm stuck on how to do that.

SELECT AVG(t.age) As AverageAge, w.yearID
FROM (SELECT w.playerID, w.age
From war_batting w, Master m
WHERE w.playerID = m.playerID AND w.yearID = 2012
ORDER by w.WAR desc
LIMIT 10) t,
war_batting w;

This displays the average of the top 10 players for that season so I'm looking to do that for every season. In the post I linked to I see they do it for every value they need but I'm looking for a way that doesn't involve repeating the same code for almost 150 years. I've programmed in Java and C++ before so naturally I'm looking for a way to loop through what I have and just keep adding 1 to the year ID up until the current year. I'm struggling to understand just how to do this. This code above also returns the yearID as 2004 and not 2012 but I'm more confident in my ability to fix that as I'm pretty sure I have in other tests. Also if the code block doesn't come out well when I first post this I'll try and fix it as this is my first post here.

Answer Source

You're not selecting anything from master table so there's no need to join with it..

Try the below query. It has an inner query that uses user-defined variables to rank based on WAR descending. Then the outer query only looks at ranks that are <= 10, and then it averages while grouping by yearID.

SELECT AVG(T.age) AS AverageAge, T.yearID
  (SELECT w.playerID, w.age, w.yearID,
         CASE WHEN @prev_year = yearID THEN @rank := @rank+1
         ELSE @rank:=1
         END as rank,
         @prev_year := yearID
    FROM war_batting w
WHERE T.rank <= 10


This way you can just add ORDER BY T.yearID DESC or ORDER BY T.yearID ASC to the end of your query and get all your result without executing the same code for each year for 150 years.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download