mysql - MYSQL连接3个表并计算累积百分比

我在尝试从下表计算累积组分数时遇到错误：

group_details:
id      name
=====================
1       Group 1
2       Group 2
3       Group 3

group_members:
id      group_id
======================
1       1
2       1
3       2
4       2
5       3

answers:
id      member_id   is_correct
=================================
1       1           1
2       1           0
3       2           1
4       2           1
5       3           1
6       3           0
7       4           0
8       4           1

我正在努力做到这一点：

Group Name            Total Members     Total Score (%)
==============================================
Group 1                  2              75.00
----------------------------------------------
Group 2                  2              50.00
----------------------------------------------
Group 3                  1              0
----------------------------------------------

运行查询时返回空结果。请看下面我的代码。

 SELECT
    ((SUM(a.is_correct) / (2 * SUM(m.id))) * 100)  as cummulative_score,
    SUM(m.id) as total_members,
    g.name
  FROM
    `group_details` AS g
    LEFT JOIN `group_members` m
      ON m.group_id = g.id
    LEFT JOIN `answers` a
      ON a.member_id = m.id
  WHERE a.is_correct = 1
  GROUP BY g.id;

最佳答案

此查询将为您提供所需的结果。请注意，根据您问题中的表名，我使用了answers作为应答表，如果它实际上被称为answer，则需要更改它。

SELECT
    g.name,
    COUNT(DISTINCT m.id) AS `Total Members`,
    ROUND(COALESCE(AVG(a.is_correct), 0) * 100, 2) AS `Total Score (%)`
  FROM
    `group_details` AS g
    LEFT JOIN `group_members` m
      ON m.group_id = g.id
    LEFT JOIN `answers` a
      ON a.member_id = m.id
  GROUP BY g.id

输出：

name        Total Members   Total Score (%)
Group 1     2               75.00
Group 2     2               50.00
Group 3     1               0.00