mysql - Count of other rows in table with the same value

Question

So I'm a bit new to mySQL and can figure out how to do this. I have a table with columns for worker, the building they work in, and the office they work in.

I'm trying to make query that will return this table with an extra column saying how many people work in the same room. so in this example Paul and Tim both work in xxx1 and so the column would say 2 but for John it would say 1 and he's the only person in that room.

| worker | office building   | room number     |
------------------------------------------------
| john   | xxx               | 0               | 
| paul   | xxx               | 1               |
| tim    | xxx               | 1               |
| Dave   | yyy               | 0               |    
| Ty     | yyy               | 1               |    
| Luke   | yyy               | 1               |

Answer 1

Try this:

SELECT t1.*,
  (SELECT COUNT(1)
  FROM mytable t2
  WHERE t2.`room number` = t1.`room number`
  )
FROM mytable t1

I advice you don't use field name with white space, use the underscore instead white space.