[thelist] SQL help needed

[Jon Haworth]

Hi list,

I'm grappling with SQL today and I'm having a bit of confusion. It's like
trying to find the soap in the bath - I just think I've got a handle on it
and then it all goes wrong :-)


A bit of background first:

We run audits on our databases to make sure our users are filling in fields
correctly, etc. The results from these audits are stored in a MySQL table
called "AuditResults", which contains the following:

- ResultID      (Primary key, autoincrement)
- ResultAuditID (ID of audit this result relates to)
- ResultStaffID (ID of staff member this result relates to)
- ResultScore   (Actual result, out of 100)

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.

So, I can get the average score across all audits for each member of staff
by doing

SELECT ResultStaffID, AVG(ResultScore) AS AverageScore
	FROM AuditResults
		GROUP BY ResultStaffID
			ORDER BY AverageScore DESC;

and it works very nicely.


Now onto the problem.

What I need to do now is get average scores for the teams, and this is where
it all goes a little pear-shaped. At the moment I'm running nested queries,
which seems bloody stupid:

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

It works (FSOV), but it's hardly efficient/beautiful, and that's annoying.

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


Any clues or pointers much appreciated: I hope I've given enough details, if
not please shout! 

Cheers
Jon



13 Southampton Place
London WC1A 2AL
Tel: 020 7404 4004

Please visit us on the Internet:
http://www.witanjardine.co.uk/

The information included in this e-mail 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 of
confidentiality.