Mysql count how many answers are correct

Question

I have table user_answers :

id | quiz_id | user_id | question_id | choosen_answer_id |
1  |    1    |    2    |      1      |        5          |  
2  |    1    |    2    |      2      |        6          |  
3  |    1    |    2    |      3      |        7          |  
4  |    1    |    3    |      1      |        5          |  
5  |    1    |    3    |      2      |        8          |  
6  |    1    |    3    |      3      |        9          |

I want to cont how many correct answers (same choosen_answer_id for same question_id ) has user with user_id = 3 compared with user_id = 2 on same quiz_id

In this example I should receive 1 because user with user_id = 3 answered correct only on question with question_id=1 (for user_id = 2, question_id = 1 choosen_answer_id = 5 and for user_id = 3, question_id = 1 choosen_answer_id = 5`).

Thanks

"correct are answers" are from user_id = 2 but it does not matter. Мaybe "correct are answers" is not in place, and my question should be: count how many answers are same for same question — Jovan
– Jovan, Commented Feb 16, 2019 at 21:41

GMB · Accepted Answer · 2019-02-16 21:42:08Z

1

I understand that you are willing to count for how many questions both users have given the same answer. You could self-join the table as follows :

SELECT COUNT(*) AS number_of_identical_answers
FROM user_answers ua2
INNER JOIN user_answers ua3 
    ON  ua3.user_id           = 3 
    AND ua3.quiz_id           = ua2.quiz_id 
    AND ua3.question_id       = ua2.question_id 
    AND ua3.choosen_answer_id = ua2.choosen_answer_id
WHERE ua2.user_id = 2

If you want to display the joined records instead of counting them, you can change COUNT(*) to a list of columns from tables ua2 (answers of user 2) and ua3 (answers of user 3).

answered Feb 16, 2019 at 21:42

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harshil Doshi · Accepted Answer · 2019-02-16 21:48:07Z

0

select user_id, count(*)
from table1 t1
join (select *from table1 where user_id = 2) t2
on t2.question_id = t1.question_id
where t2.choosen_answer_id  = t1.choosen_answer_id 
group by t1.user_id

answered Feb 16, 2019 at 21:48

Harshil Doshi

3,6123 gold badges18 silver badges41 bronze badges

Collectives™ on Stack Overflow

Mysql count how many answers are correct

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related