mysql - 如何计算单个查询中每个用户的百分比？

Question

我进行了一个查询，显示每个用户为每个部门回答的问题数量以及每个用户在为该部门回答问题时正确和错误的百分比。

所以，我尝试这样的查询，

SELECT  d.department_id, d.department_name as department, 
        a.username, COUNT(a.username)total,
        (  (COUNT(r.is_correct)*100) / 
              (   SELECT COUNT(a.username) total
                  FROM qa_report r
                  LEFT JOIN tbl_user a ON (r.user_id = a.admin_id)
                  LEFT JOIN department d on (a.department_id = d.department_id)
                  WHERE d.department_name = 'Dept1' AND is_correct='yes'
              )
        ) as correct_percent
FROM qa_report r
LEFT JOIN tbl_user a ON (r.user_id = a.admin_id)
LEFT JOIN department d on (a.department_id = d.department_id)
WHERE d.department_name = 'Dept1'
GROUP BY a.username

这就是结果

total 列是用户对该部门回答的问题的数量。

在此总数中，正确答案和错误答案混合在一起。

但是当我尝试计算每个部门的正确/不正确百分比时， Correct_percent 列仍然错误。

问题出在这一行，

(  (COUNT(r.is_correct)*100) / 
          (   SELECT COUNT(a.username) total
              FROM qa_report r
              LEFT JOIN tbl_user a ON (r.user_id = a.admin_id)
              LEFT JOIN department d on (a.department_id = d.department_id)
              WHERE d.department_name = 'Dept1' AND is_correct='yes'
          )
    ) as correct_percent

我需要像这样获得该地方每个用户的正确百分比，

(答案的正确百分比*100)/用户回答的总问题
例如。 John => 总数:10，正确 5，错误 5，正确 %=50%

我非常感谢您的任何建议。

Answer 1

我现在可以解决这个问题了。当我尝试计算百分比时我错了。这是有效的查询，

SELECT  d.department_id, d.department_name as department, 
    a.username, COUNT(a.username)total,
    (  (COUNT(CASE WHEN r.is_correct='yes' THEN 1 END)*100) / ( COUNT(a.username)) ) as correct_percent,
    (  (COUNT(CASE WHEN r.is_correct='no' THEN 1 END)*100) / ( COUNT(a.username)) ) as incorrect_percent
FROM qa_report r
LEFT JOIN tbl_user a ON (r.user_id = a.admin_id)
LEFT JOIN department d on (a.department_id = d.department_id)
WHERE d.department_name = 'Dept1'
GROUP BY a.username

希望这对某人有帮助。