La butto lì:

codice:
SELECT DISTINCT COUNT(post.id_room), rooms.room, rooms.id_room
FROM post, rooms
WHERE post.id_room = rooms.id_room
GROUP BY post.id_room
UNION
SELECT DISTINCT rooms.room, rooms.id_room
FROM post, rooms
WHERE NOT (post.id_room = rooms.id_room)