Complex Aggregate Query
Posted on 2004-10-25
I currently use the following query to basically show how often we win/lose/tie based on the number of points we score.
Count(IIf((theirscore < ourscore), 1, Null)) AS WIN,
Count(IIf((theirscore > ourscore), 1, Null)) AS LOSS,
Count(IIf((theirscore = ourscore), 1, Null)) AS TIE,
FROM game g, team t
AND g.theirid = t.teamid
AND t.TEAMLEAGUE = 'NERF'
AND g.finalscore > 0
group by ourscore
I then paste this in Excel to calulate the win ratio for each line [WIN/(WIN+LOSS+TIE)]. I'd like to add that to the above query. That's the easy part. Now the hard part. For each line item, I'd also like to include the win ratio for the line, the line above, and the line below all added together.
Here's an example of what I want to see.
SCORE WIN LOSS PUSH AVG GRPAVG
3 1 2 0 .333 .571 <=[(1+3)/(1+3+2+1+0+0)]
6 3 1 0 .750 .600 <=[(3+1+2)/(3+1+2+1+2+1+0+0+0)]
7 2 1 0 .667 .667 <=[(2+3+1)/(2+3+1+1+1+1+0+0+0)]
9 1 1 0 .500 .600 <=[(1+2)/(1+2+1+1+0+0)]
Normally I'd loop through the result set to get this info, but I'm using an access DB via the web, so it's not quite that easy. I know I'm forgetting some details, so if you have any questions, let me know.