Posted by Jerry Stuckle on 08/12/07 14:53

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

This isn't a PHP question. Try asking in a database newsgroup - for
instance, if you're using MySQL, try comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]