AVG from specific rows with a JOIN

[jcanker]

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.  

 

 

[jcanker]

I love it when I can answer my own question for anyone who happens to be looking for a solution later on and find it here

 

Prior to the JOIN to attach first and last names to the userID, the query is:

 

SELECT userID, AVG( cat1Val ) , AVG( cat2Val ) , AVG( cat3Val ) , AVG( cat4Val ) , AVG( cat5Val ) 
FROM evaldata
WHERE evalID =1
GROUP BY userID
ORDER BY AVG(cat3VAL) ASC 

 

This would allow for sortable tables on the PHP side as well by passing the column name that the customer clicked on to sort on

[Christian F.]

You should probably have moved those categories into a table of their own, and then JOINed it in. Along with the user table to get the name for each player.

Then you'd get a query which would look something like this:

SELECT u.id, u.firstname, u.lastname, s.name, AVG(s.value)
FROM evaldata AS ed
INNER JOIN user AS u ON u.id = ed.user_id
INNER JOIN scores AS s ON s.id = ed.score_id
GROUP BY u.id, s.id