Anton wrote:
> On 12 aug, 22:24, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>> Anton wrote:
>>> 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...
>>
>> What is the logic behind the numbers 133, 111, 98, 86, 73?
>
> I'm sorry my explanation didn't work...
>
> You have to look to the first table 'result'. player 1 has played 3
> times. The first time he played with country 5 and scored 40 points.
> The second time he played country 3 and made 22 points en the third
> time he played country 1 with 31 points. To calculate your final
> score, you have to look at all points everybody scored with the
> particularly country and what percentage your score was. So each
> player has 5 percentages, one for each country. Take a look at player
> 1. My 40 points for country 5 are 45% of all the points scored with
> country 5 (my 40, player 7 33 and player 4 16 gives a total of 89).
> This goes likewise for country 1 (31/51=61%) country 4 (0/153=0%)
> country 3 (22/82=27%) and country 2 (0/123=0%). So player 1 has
> 61+0+27+0+45=133. This calculation goes for every player. There are
> gonna be more than 5 players because not everyone plays along every
> time.
>
> Hope you can help me...

Sorry for the delay. Next time you have a question like this, please post a
CREATE TABLE export from phpmyadmin and the associated exported INSERT
statements.

Anyway, here is your query:

SELECT
`player_id`,
ROUND( SUM(`points`/(
SELECT SUM(`points`)
FROM `result` `p`
WHERE `p`.`country_id` = `r`.`country_id` ) ) *100 ) `pct`
FROM `result` `r`
GROUP BY `player_id`
ORDER BY `pct` DESC