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,
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 FROM (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 ORDER BY yearID ASC, WAR DESC )T WHERE T.rank <= 10 GROUP BY T.yearID
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.