Help on streamlining a query in MySQL

[Ben E Lou]

I'm trying to look at some era comparisons in FOF. I'm wanting to start with something basic: player ypa for a season versus league-wide ypa for the season. The idea here is that if a guy gets ypa above the league-wide average, he gets a score over 100. It it's below the league-wide average, his score is under 100. Here's the query I created (not certain if this *should* work, even...)

SELECT sum(s.passyards)/sum(s.passattempts) AS ypa, s.year,  lastname, firstname,
((sum(s.passyards)/sum(s.passattempts))/(SELECT sum(s1.passyards)/sum(s1.passattempts) FROM fof_playergamestats s1 where s1.year=s.year))*100 AS pct,
(SELECT sum(s1.passyards)/sum(s1.passattempts) FROM fof_playergamestats s1 where s1.year=s.year) AS leagueypa
FROM fof_playergamestats s
JOIN fof_playerhistorical h on h.id=s.playerid
GROUP BY s.playerid, s.year
HAVING sum(s.passattempts)>319
ORDER BY pct desc limit 50

It has been running for 10 minutes already, on a relatively fast server. That seems excessive, and I suspect that it's because of something I did wrong there in the nesting. Li'l help?

[Drake]

You need a better join between s and s1 than just Year. Those are pass attempts by player, right? Since you're summing everything, I'd think you'd want year and playerid.