So I have a table like this and have been trying to figure out a way to SELECT all hotelNo that have > 1 type='double' room. Haven't been able to figure out a way help would be appreciated.
MariaDB [hotel]> SELECT * FROM room;
+--------+---------+--------+-------+
| roomNo | hotelNo | type | price |
+--------+---------+--------+-------+
| 1 | 1 | single | 80 |
| 1 | 2 | single | 90 |
| 1 | 3 | single | 80 |
| 1 | 4 | single | 80 |
| 1 | 5 | single | 80 |
| 1 | 6 | double | 100 |
| 1 | 7 | single | 80 |
| 2 | 1 | single | 80 |
| 2 | 2 | single | 80 |
| 2 | 3 | single | 80 |
| 2 | 4 | single | 80 |
| 2 | 5 | single | 80 |
| 2 | 6 | single | 80 |
| 2 | 7 | single | 80 |
| 3 | 1 | double | 95 |
| 3 | 2 | double | 100 |
| 3 | 3 | double | 95 |
| 3 | 4 | double | 95 |
| 3 | 5 | double | 95 |
| 3 | 7 | double | 95 |
| 4 | 1 | vip | 120 |
| 4 | 2 | vip | 120 |
| 4 | 3 | vip | 120 |
| 4 | 4 | vip | 120 |
| 4 | 5 | double | 100 |
| 5 | 1 | single | 85 |
| 5 | 2 | other | 120 |
| 5 | 3 | other | 110 |
| 5 | 4 | double | 95 |
| 5 | 5 | double | 100 |
+--------+---------+--------+-------+
30 rows in set (0.001 sec)
I had:
MariaDB [hotel]> SELECT hotelNo, type, COUNT(*) FROM room GROUP BY type HAVING COUNT(*) > 2;
But that just gives me the total number of each room type:
+---------+--------+----------+
| hotelNo | type | COUNT(*) |
+---------+--------+----------+
| 1 | single | 14 |
| 6 | double | 10 |
| 1 | vip | 4 |
+---------+--------+----------+
3 rows in set (0.001 sec)
I want to get an output showing which hotelNo's have more than two double rooms.
You want hotels that have more than 1 double room. I would suggest filtering on type = 'double', aggregating by hotelno, and using a having clause to ensure that there is more than one record in the group:
select hotelno
from room
where type = 'double'
group by hotelno
having count(*) > 1