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...

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?

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...

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

Here is a query to give the same result, using a JOIN instead of a
correlated sub-select

SELECT
`player_id`,
ROUND( SUM( `points` / `country_total`) *100 ) `pct`
FROM `result` `r`
JOIN (
SELECT
`country_id`,
SUM( `points` ) `country_total`
FROM `result` `p`
GROUP BY `country_id` ) `ct` USING(`country_id`)
GROUP BY `player_id`
ORDER BY `pct` DESC