Cris Cris -4 years ago 76
MySQL Question

Get the total correct answer in exam (create a view table mysql)

Hello I want to create a view table that count the correct answer of the each users. The schema of my table is something like this.

User exam table

user_id | questionnaire_id | answer_id
1 | 1 | 3
1 | 2 | 1
2 | 1 | 4
2 | 2 | 2


Answer table

answer_id questionnaire_id is_correct
1 1 1
2 1 0
3 1 0
4 1 0
1 2 0
2 2 1
3 2 0
4 2 0


Thanks

Answer Source

Because is_correct has a value of 1, you can sum and derive their total score:

select e.user_id, sum(a.is_correct) score
from exam_table e 
left join answer_table a 
  on a.questionnaire_id = e.questionnaire_id 
  and a.answer_id = e.answer_id
group by e.user_id;

Here is a functional example with a new user with a perfect score

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download