mysql - 对分组子查询求和

Question

我需要划分子查询中两列的总和，并按第三个字段分组，如下所示:

Team    |    Score     |    MaxScorePossible
--------------------------------------------
A       |      10      |     15
A       |      12      |     20
B       |      5       |     15
B       |      7       |     20

我的代码是这样的:

SELECT (sumScore/sumMaxScore) as Percentage
FROM
(SELECT 
   sum(Score) as sumScore 
   FROM tableScore
   GROUP BY Team ) tbl1,
(SELECT 
   sum(MaxScorePossible) as sumMaxScore 
   FROM tableScore
   GROUP BY Team ) tbl2,
GROUP By Team

我希望的输出是这样的:A => 0.62，B => 0.34

问题显然是我正在对子查询和父查询进行分组，但我不知道如何对一个查询进行分组并导致另一个查询进行类似的分组。

Answer 1

您不需要子查询。

SELECT Team, SUM(Score)/SUM(MaxScorePossible) AS Percentage
FROM tableScore
GROUP BY Team

如果您确实想使用子查询，则必须加入它们。

SELECT tbl1.Team, (sumScore/sumMaxScore) as Percentage
FROM
    (SELECT 
       Team, sum(Score) as sumScore 
       FROM tableScore
       GROUP BY Team ) tbl1
JOIN
    (SELECT 
       Team, sum(MaxScorePossible) as sumMaxScore 
       FROM tableScore
       GROUP BY Team ) tbl2 
ON tbl1.Team = tbl2.Team