Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE hotels (room_id int, user_id int, user_name text);
    
INSERT INTO hotels VALUES
  (1, 1, 'Bob')
, (2, 11, 'John')
, (2, 22, 'Jane')
, (3, 111, 'Jessi')
, (3, 222, 'Arthur')
, (3, 333, 'Dorra')
, (4, 444, 'Carl')
, (4, 555, 'James')
, (4, 666, 'Noel')
, (5, 33, 'Bill')
, (5, 44, 'Bell')
, (6, 55, 'Tina')
, (6, 66, 'Timor')
;
13 rows affected

-- counts for all room sizes
SELECT room_size
     , count(*) AS rooms
FROM  (
   SELECT count(*) AS room_size
   FROM   hotels
   GROUP  BY room_id
   ) sub
GROUP  BY room_size
ORDER  BY room_size;


room_size
rooms


1
1

2
3

3
2


-- your desired (pivoted) result
SELECT single, double, tripple
FROM   crosstab(
   $$
   SELECT 'any_hotel' AS dummy
        , room_size
        , count(*)::int AS rooms
   FROM  (
      SELECT count(*) AS room_size
      FROM   hotels
      GROUP  BY room_id
      ) sub
   GROUP  BY room_size
   ORDER  BY room_size
   $$
   ) AS ct (hotel text, single int, double int, tripple int);


single
double
tripple


1
3
2