sql - mysql group by confusion -

so have table;

mysql> describe player_weapon_stats;

+------------+------------------+------+-----+---------+----------------+ | field      | type             | null | key | default |          | +------------+------------------+------+-----+---------+----------------+ | id         | int(10) unsigned | no   | pri | null    | auto_increment | | players_id | int(10) unsigned | no   |     | null    |                | | weapons_id | int(10) unsigned | no   |     | null    |                | | matches_id | int(10) unsigned | no   |     | null    |                | | hits       | int(10) unsigned | no   |     | null    |                | | shots      | int(10) unsigned | no   |     | null    |                | | kills      | int(10) unsigned | no   |     | null    |                | | acc        | decimal(4,2)     | no   |     | null    |                | +------------+------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) 

with lots of rows (currently around 400k) this;

mysql> select * player_weapon_stats order id asc limit 5;

+----+------------+------------+------------+------+-------+-------+-------+ | id | players_id | weapons_id | matches_id | hits | shots | kills | acc   | +----+------------+------------+------------+------+-------+-------+-------+ |  1 |          1 |          1 |          1 |    5 |     0 |     1 |  0.00 | |  2 |          1 |          2 |          1 |  133 |   437 |     2 | 30.43 | |  3 |          1 |          3 |          1 |  247 |   896 |     8 | 27.57 | |  4 |          1 |          4 |          1 |    0 |    11 |     0 |  0.00 | |  5 |          1 |          5 |          1 |   35 |    59 |     9 | 59.32 | +----+------------+------------+------------+------+-------+-------+-------+ 5 rows in set (0.02 sec) 

so multiple weapon stats per player per match recorded

what im trying highest acc each weapon_id based on sum total of each player

the returned rows should equal in count number of weapons (in case 8)

this tried;

mysql> select players_id, weapons_id, sum(hits) hits, sum(shots) shots, sum(kills) kills, (format(hits / shots, 4) * 100) acc player_weapon_stats group weapons_id order acc desc; // no player association sum totals players  mysql> select players_id, weapons_id, sum(hits) hits, sum(shots) shots, sum(kills) kills, (format(hits / shots, 4) * 100) acc player_weapon_stats group weapons_id, players_id order acc desc, weapons_id asc limit 10; // incorrect acc , around 25k rows returned  mysql> select players_id, weapons_id, sum(hits) hits, sum(shots) shots, sum(kills) kills, (format(hits / shots, 4) * 100) acc player_weapon_stats group players_id, weapons_id order acc desc, weapons_id asc; // appears correct acc, , correct totals returns around 25k rows 

ive tried lot of variations of above , whatever else came mind @ time im still stuck.. think ive been staring @ long

can me out?

---- edit

the sample data used bit small compile results there multiple entries each weapons_id each players_id added form "average/overall" player/weapon;

http://pastebin.com/q1n5mscu

as see added totals each weapon 4 players.. expected result similar 1 row per weapon

im not sure how else explain it

---- 2nd edit

mysql> select players_id, weapons_id, max(acc) (select weapons_id, players_id, avg(acc) acc player_weapon_stats group players_id, weapons_id) t1 group weapons_id;     +------------+------------+-----------+     | players_id | weapons_id | max(acc)  |     +------------+------------+-----------+     |          1 |          0 | 25.000000 |     |          1 |          1 |  0.000000 |     |          1 |          2 | 84.995000 |     |          1 |          3 | 99.990000 |     |          1 |          4 | 99.990000 |     |          1 |          5 | 94.290000 |     |          1 |          6 | 70.250000 |     |          1 |          7 | 99.990000 |     |          1 |          8 | 99.990000 |     +------------+------------+-----------+     9 rows in set (0.33 sec) 

---- 3rd edit

what appears solution based on jcrummacks queries;

mysql> select players_id, weapons_id, hits, shots, kills, max(acc) ( select players_id, weapons_id, sum(hits) hits, sum(shots) shots, sum(kills) kills, avg(acc) acc player_weapon_stats group players_id, weapons_id order weapons_id asc, avg(acc) desc) t1 group weapons_id; +------------+------------+------+-------+-------+-----------+ | players_id | weapons_id | hits | shots | kills | max(acc)  | +------------+------------+------+-------+-------+-----------+ |        202 |          0 |    1 |     3 |     0 | 25.000000 | |       1544 |          1 |    1 |     0 |     0 |  0.000000 | |       3034 |          2 |    8 |    11 |     0 | 84.995000 | |        952 |          3 |   16 |    16 |     0 | 99.990000 | |       3493 |          4 |    1 |     1 |     0 | 99.990000 | |        839 |          5 |   33 |    35 |     2 | 94.290000 | |        734 |          6 |  366 |   521 |     5 | 70.250000 | |       2643 |          7 |    1 |     1 |     0 | 99.990000 | |       3227 |          8 |    1 |     1 |     0 | 99.990000 | +------------+------------+------+-------+-------+-----------+ 9 rows in set (0.72 sec) 

i'm sort of reading between lines here of think looking , assuming on recent version of mysql (needs derived table support) if i'm not quite on looking maybe point in right direction.

select   players_id,   weapons_id,   max(acc) (   select     weapons_id,     players_id,     avg (acc) acc       player_weapon_stats   group     players_id,     weapons_id   order      weapons_id asc,      avg(acc) desc) t1 group    weapons_id 

hopefully i'm going in direction trying go.