Posted by Anton on 08/12/07 13:15

Hi everyone,

Warning: Bad English ahead...

Me and some other guys regularly play Axis & Allies. A war game, you
play a county and get some points. I've created a mysql db to maintain
a list of best players. What I have is this table 'result':

id player_id country_id result_id played_game_id
points
38 7 4 6 3 32
37 2 3 6 3 35
36 4 1 6 3 20
35 3 2 6 3 39
34 1 5 6 3 40
33 7 5 4 2 33
32 4 2 2 2 35
31 3 4 5 2 76
30 2 1 1 2 0
29 1 3 2 2 22
23 7 2 5 1 49
22 4 5 2 1 16
21 3 4 2 1 45
20 2 3 4 1 25
19 1 1 4 1 31

'Cause there are 5 players each time, there are 5 rows for every
played game. A single row has: a player_id, a country_id, a result_id
(never mind), a played_game_id (also never mind) and finally the
points.

The calculation is: For each country there is a total of scored
points. (ex. country_id 1 = 20 + 0 + 31 = 51). I am player 1 and
scored 31 points of this 51 = 61%. My total score is the sum of each
percentage I scored for each county.

I have this:

SELECT player_id, country_id, points/(SELECT SUM(points) FROM result
WHERE country_id = 1) AS pct
FROM result
WHERE country_id = 1
GROUP BY player_id
ORDER BY pct DESC

which gives me this:

player_id country_id pct
1 1 0.61
4 1 0.39
2 1 0.00

But what I want is this:

player_id pct
1 133
3 111
5 98
4 86
2 73

Could someone help me please??? Thanks...

[Back to original message]