CODEWITHSUNDEEP
mysql
Cloud
Cloud

Reputation: 1034

How to calculate percent of each user in single query?

I make a query, that show each user answer amount of question for each department and correct and incorrect percent of each user when they answer question for that department.

So, I try a query like this,

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

This is the result,

query result

total column is the amount of question's answer that user answer for that department.

In this total, correct and incorrect answer are mixing.

but the correct_percent column is still wrong when I try to calculate correct/incorrect percent of each department.

The problem is at this line,

(  (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

I need to get the correct percent of each user at that place like this,

(correct percent of answer*100)/total question that user answer eg. John => total: 10, correct 5, incorrect 5, correct %=50%

I very appreciate for any suggestion.

Upvotes: 0

Views: 90

Answers (1)

Cloud
Cloud

Reputation: 1034

I can solve this problem now. I was wrong when I try to calculate percentage. Here is the query that work,

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

Hope this help for someone.

Upvotes: 1

Related Questions