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;
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.
Comments
Post a Comment