SUM n rows of each group with ORDER BY and LIMIT where the LIMIT is based off of another table
The solution I ended up implementing was to break up the query into simpler ones based on the different permutations of the positions and formations. I also needed to add several tables to store intermediate results. I ran all the queries (around 42) in a transaction.

This worked out pretty well and completed in reasonable time without overloading the server resources.

I guess trying to do it in one giant query was a bad decision from the beginning. Breaking it up into smaller queries allowed the process to be maintainable and flexible.