Calculate the number of single / double / tripple rooms from a rooming table

Question

I have a hotels table and I would like to know how many single/double/triple rooms I have.

Sample data:

room_id	user_id	user_name
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

Examples:
room_id 1 has only a single occurrence. That's one "single" room.
room_id 2 appears twice so it's a "double" room. Same for room_id 5 and 6. So we have a total of 3 "double" rooms.

I need an SQL query to get the count of single/double/triple rooms. Like this:

single	double	tripple
1	3	2

Answer 1

First extract the number of occurrences by room_id and then count single, double and tripple rooms using conditional aggregation.

select 
    count(*) filter (where c = 1) single_room,
    count(*) filter (where c = 2) double_room,
    count(*) filter (where c = 3) tripple_room
from 
(
 select room_id, count(*) c from _table group by room_id
) t;

Answer 2

A more general query to get 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;

db<>fiddle here

The result is pivoted from your desired result:

 room_size | rooms 
-----------+-------
         1 |     1
         2 |     3
         3 |     2

To pivot (and extract any room sizes from the same basic query) use crosstab(). See:

• PostgreSQL Crosstab Query

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);

db<>fiddle here

 single | double | tripple 
--------+--------+---------
      1 |      3 |       2

For a small number of room sizes, this is more verbose than multiple counts with the FILTER clause. But it should be faster for big tables. If performance does not matter, you might as well use the FILTER query provided by Stefanov.

Related:

• Postgres COUNT number of column values with INNER JOIN