snekse
asked on
Complex Aggregate Query
I currently use the following query to basically show how often we win/lose/tie based on the number of points we score.
SELECT
Count(IIf((theirscore < ourscore), 1, Null)) AS WIN,
Count(IIf((theirscore > ourscore), 1, Null)) AS LOSS,
Count(IIf((theirscore = ourscore), 1, Null)) AS TIE,
ourscore
FROM game g, team t
WHERE 1=1
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.
SELECT
Count(IIf((theirscore < ourscore), 1, Null)) AS WIN,
Count(IIf((theirscore > ourscore), 1, Null)) AS LOSS,
Count(IIf((theirscore = ourscore), 1, Null)) AS TIE,
ourscore
FROM game g, team t
WHERE 1=1
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+
7 2 1 0 .667 .667 <=[(2+3+1)/(2+3+1+1+1+1+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.
ASKER
What do you mean by "create a function"? In access? Or just in the SQL statement?
This can't be done via sub-queries?
This can't be done via sub-queries?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If this is the case you would have to create a function that queries the values from the specified records in your query, adds them together returns the answer you want for each row in your query.
If you want more information than this I'll try to prduce a function for you.
Regards
PF