[thelist] SQL help needed

Warden, Matt mwarden at mattwarden.com
Mon Sep 3 09:42:30 CDT 2001


On Sep 3, Jon Haworth had something to say about [thelist] SQL help needed

>There's a separate MySQL table ("Staff") that holds info on the staff - the
>important field here is StaffTeamID, which is the ID of the team that this
>person belongs to. These teams are then held in a third table ("Teams",
>unsurprisingly), of which the relevant fields are TeamID and TeamName.
>
...
>SELECT ResultStaffID, AVG(ResultScore) AS AverageScore
>	FROM AuditResults
>		GROUP BY ResultStaffID
>			ORDER BY AverageScore DESC;
>
...
>get the teams {
>	get the staff in current team {
>		get the average result for this member of staff
>		add it to the team's total
>		increase a counter
>	}
>	divide the total by the counter to get the team average and stick it
>in an array
>}
>sort the array
>display the results

OUCH!  =)

>There must be a way of reducing the pseudocode above to an SQL statement. If
>the team ID was stored in the AuditResults table I could just add a "WHERE
>TeamID = x" to the SQL above, but again this just seems like a kludge
>instead of the proper way to approach it (not a big fan of storing redundant
>data, personally).

Yeah, especialy since you don't need to have the data in the AuditResults
table. Now, if mysql supported subqueries, this would be a tad
easier. Let's see... (untested, might need a tweak):

SELECT s.StaffTeamID, AVG(r.ResultScore) As AverageScore
FROM AuditResults r, Staff s
WHERE r.ResultStaffID=s.StaffID
	AND s.StaffTeamID NOT NULL
GROUP BY s.StaffTeamID
ORDER BY AverageScore DESC

Now, this gives the averages of all individual scores for a team, NOT the
average of the team members' averages. My mathbrain ain't running well
this early in the morning, but I don't think this matters mathematically:

(1+2+3+4)/4 = 2.5
((1+2)/2 + (3+4)/2)/2 = 2.5

So, assuming that sql works above, I think you are getting the results you
want.

HTH some,


--
mattwarden
mattwarden.com





More information about the thelist mailing list