sql subtotals and grouping

[moleboy]

I'm stuck with my sp. I'm trying to get a recordset containing assessment, staff, team, score, ave score, team ave score.

I'm stuck on the ave scores

SELECT Score, TeamID, staffID, (??) AS AveScore, (??) as TeamAveScore
FROM tblAssessment
GROUP BY TeamID, Score, staffID
ORDER BY TeamID

I keep getting group by errors when using subqueries within the select. Any ideas on how best to do this??

 

[mwolf00]

I think you'll need to remove "Score" from your original query - why do you want it in each row? If you do want it, you'll need two derived tables - my code uses only one...

SELECT a.TeamID, a.staffID, avg(a.score) AS AveScore, dt.teamAvg
FROM  tblAssessment a
JOIN (SELECT teamID, avg(score) as teamAvg FROM tblAssessment GROUP BY teamID) dt ON a.teamID = dt.teamID
GROUP BY TeamID, staffID
ORDER BY TeamID

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[moleboy]

Thanks, thats done the trick.