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 |