I'm still on the baseball progam here.
The players are evaluated prior to the season and rated in some broad areas, such as "fielding" (ability to throw/catch grounders, flies, long throws, etc), "pitching" and "batting"
The userID for each player is in TABLE: users
I'd like this application to be broad enough that the local rec soccer and softball programs could have access to it as well. Clearly they have different rating categories and different rating scales. Additionally, my son's program is discussing moving from their current 1-5 with 1 being best, 5 being worst to something like a 1-10 with 10 as best.
I allow each program to define an evaluation in TABLE:evaluations which stores the seasonID the eval is for, the category names and the best possible rating, worst possible rating, for each category, etc.
Each player is likely to have multiple scores (one or as many as 7) assigned by different "judges" who are watching and evaluating the players in the different categories. Each rating is stored as individual row in TABLE: evaldata. Columns are userID, evalID, cat1 (FLOAT 5,2), cat2(FLOAT 5,2), cat3(FLOAT 5,2), cat4(FLOAT 5,2), cat5(FLOAT 5,2), cat6(FLOAT 5,2), cat7(FLOAT 5,2), cat8(FLOAT 5,2), cat9(FLOAT 5,2), cat10(FLOAT 5,2)
I also have (as some of you know) TABLE registrations which holds the userID of each player registered for the current season
RIght now I have workign PHP that pulls the userIDs (distinct) from TABLE registrations. For each player, it then finds the rows entered in TABLE evaldata, then averages each category, and provides an overall average.
Running this script on the 100-150 players an any particular age group is pretty slow.
MY QUESTION IS:
Is there a way to write the SQL that it can calculate the averages in each category for each player as it retrieves all the rows for a given seasonID? I'll then JOIN or UNION it to the users table to put a name to the player's userID.