[thelist] SQL help needed

[Jon Haworth]

> > On Sep 3, Jon Haworth had something to say about [thelist] SQL help
needed
> But then Matt Warden came to the rescue with:

> 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.

Cheers Matt, I think I'm there...

    SELECT Teams.TeamName
         , AVG (AuditResults.AuditResult) AS AverageScore
      FROM Teams
         , AuditResults
         , Staff
     WHERE AuditResults.StaffID = Staff.StaffID
       AND Staff.StaffTeamID = Teams.TeamID
  GROUP BY TeamName
  ORDER BY AverageScore DESC

This gives me:

TeamName     AverageScore
Team one     98
Team two     93
Team three   74


Which is exactly right :-)

Of course, it might be working via a big coincidence (although the maths
checks out OK), so I'd welcome any improvements. 


Cheers
Jon



**********************************************************************
'The information included in this Email is of a confidential nature and is 
intended only for the addressee. If you are not the intended addressee, 
any disclosure, copying or distribution by you is prohibited and may be 
unlawful. Disclosure to any party other than the addressee, whether 
inadvertent or otherwise is not intended to waive privilege or confidentiality'

**********************************************************************