mysql group by confusion

so i have this table;

mysql> describe player_weapon_stats;

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          开发者_运维知识库|
+------------+------------------+------+-----+---------+----------------+
| 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) like this;

mysql> select * from player_weapon_stats ORDER BY 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 are recorded

what im trying to do is to get the highest acc for each weapon_id based on the sum total of each player

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

this is what i tried;

mysql> SELECT players_id, weapons_id, SUM(hits) AS hits, SUM(shots) AS shots, SUM(kills) AS kills, (FORMAT(hits / shots, 4) * 100) AS acc FROM player_weapon_stats GROUP BY weapons_id ORDER BY acc DESC;
// no player association so SUM totals up all players together

mysql> SELECT players_id, weapons_id, SUM(hits) AS hits, SUM(shots) AS shots, SUM(kills) AS kills, (FORMAT(hits / shots, 4) * 100) AS acc FROM player_weapon_stats GROUP BY weapons_id, players_id ORDER BY acc DESC, weapons_id ASC LIMIT 10;
// incorrect acc and around 25k rows returned

mysql> SELECT players_id, weapons_id, SUM(hits) AS hits, SUM(shots) AS shots, SUM(kills) AS kills, (FORMAT(hits / shots, 4) * 100) AS acc FROM player_weapon_stats GROUP BY players_id, weapons_id ORDER BY acc DESC, weapons_id ASC;
// appears correct acc, and correct totals but returns around 25k rows as well

ive tried a lot of variations of the above and whatever else came to mind at the time but im still stuck.. i think ive been staring at it too long

can anybody help me out?

---- edit

the sample data i used is a bit too small to compile into results as there would be multiple entries for each weapons_id for each players_id which then would be added together to form an "average/overall" for that player/weapon;

http://pastebin.com/Q1N5mScU

as you see its added up totals for each weapon for the 4 players.. so the expected result would be similar to that but simply one row per weapon

im not sure how else to explain it

---- 2nd edit

mysql> SELECT players_id, weapons_id, MAX(acc) FROM (SELECT weapons_id, players_id, AVG(acc) AS acc FROM player_weapon_stats GROUP BY players_id, weapons_id) AS t1 GROUP BY 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 to be the solution based on jcrummacks queries;

mysql> SELECT players_id, weapons_id, hits, shots, kills, MAX(acc) FROM ( SELECT players_id, weapons_id, SUM(hits) AS hits, SUM(shots) AS shots, SUM(kills) AS kills, AVG(acc) AS acc FROM player_weapon_stats GROUP BY players_id, weapons_id ORDER BY weapons_id ASC, AVG(acc) DESC) AS t1 GROUP BY 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 the lines here of what i think your looking for and also assuming your on a fairly recent version of mysql (needs derived table support) even if i'm not quite on what your looking for maybe this will point you in the right direction.

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

hopefully i'm going in the direction you were trying to go.

It sounds as though you want one row per weapon, with the maximum accuracy achieved by any player. If so, try the following:

SELECT weapons_id, 
       SUM(hits) AS hits, 
       SUM(shots) AS shots, 
       SUM(kills) AS kills, 
       MAX(acc) AS acc 
FROM (SELECT players_id, 
             weapons_id, 
             SUM(hits) AS hits, 
             SUM(shots) AS shots, 
             SUM(kills) AS kills, 
             FORMAT(SUM(hits) / SUM(shots), 4) * 100 AS acc 
      FROM player_weapon_stats 
      GROUP BY players_id, weapons_id) SQ
GROUP BY weapons_id