[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