MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE answers(room INT, answer INT);
INSERT INTO answers VALUES(1,3),(2,6),(2,5),(2,6);
SELECT a1.room,  a2.answer, SUM( a1.answer=a2.answer ) AS answerCount 
  FROM answers a1
  JOIN ( SELECT DISTINCT answer FROM answers ) a2
 GROUP BY a1.room, a2.answer
 


room
answer
answerCount


1
3
1

1
5
0

1
6
0

2
3
0

2
5
1

2
6
2