mysql - 将查询的一个结果除以另一个查询的结果

Question

我能跑

SELECT killedby, COUNT(killedby) killcount FROM games_playerinfo WHERE killedby != '' GROUP BY killedby

得到

killedby    killcount
Player1         1
Player2         1

我可以跑

SELECT ign, COUNT(ign) numberofgames FROM games_playerinfo GROUP by ign

得到

ign numberofgames
player1 7
player2 3
player3 3
player4 3

我想将第一个查询的每个结果除以第一个查询中每个用户的第二个查询的结果。

所以结果应该是

ign      avgkillcount
player1  0.1429
player2  0.333333

我想在一个查询中执行此操作，这样我就可以限制结果。我尝试过使用 INNER JOINS，但我认为我对连接的理解不够深入，甚至不知道它是否可以实现我正在尝试做的事情。

Answer 1

查询:

SELECT g1.killedby ign,
       COUNT(g1.killedby)/(SELECT COUNT(ign) numberofgames 
                                  FROM games_playerinfo 
                                  WHERE ign = g1.killedby
                                  GROUP by ign) avgkillcount
FROM games_playerinfo g1
WHERE g1.killedby != '' 
GROUP BY g1.killedby