Include COUNT (including 0) of grouped records

I have a table like below

answers:
| room | answer |
|------|--------|
| 001  | 3      |
| 002  | 6      |
| 002  | 5      |
| 002  | 6      |

I want to count the answers for each room. That’s pretty easy, but I also want the zero values for each room and each possible answer (distinct of all answers) like shown below

| room | answer | answerCount |
|------|--------|-------------|
| 001  | 3      | 1           |
| 001  | 5      | 0           |
| 001  | 6      | 0           |
| 002  | 3      | 0           |
| 002  | 5      | 1           |
| 002  | 6      | 2           |

I already had a hard time solving this problem, hope you guys can help me? 🙂

Answer

One option would be using a self join where one side of join statement containing a distinctly selected set for answers

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

Demo

Advertisement

Answer