SQL: combining a total count along with a conditional count

user_12238262 0
Viewed 79

I would like to return the total number of rooms in a hotel, and also provide the total number of rooms in that hotel that have more than 2 bedrooms. My code returns the total number of rooms in each hotel that have more than 2 bedrooms, but it does not return the total number of rooms in the hotel. Instead, it only returns the total number of rooms that have more than 2 bedrooms.

Is there a simple way to modify my code to resolve this issue?

I have the following code:

SELECT
    hotel.hotel_id,
    hotel.hotel_name,
    COUNT(room.room_no) || ' total rooms ' || COUNT(room.room_no) 
    || ' have more than 2 bedrooms ' AS ACCOMODATION_AVAILABLE
FROM
    hotel
INNER JOIN
    room
ON
    hotel.hotel_id = room.hotel_id
WHERE
    room_bedrooms > 2
GROUP BY
    hotel.hotel_id, hotel.hotel_name;
pivot count oracle sql string
2 Answers

Use conditional aggregation:

SELECT h.hotel_id, h.hotel_name,
       COUNT(*) as total_roms,
       SUM(CASE WHEN r.room_bedrooms > 2 THEN 1 ELSE 0 END) as num_3plus
FROM hotel h JOIN
     room r
     ON h.hotel_id = r.hotel_id
GROUP BY h.hotel_id, h.hotel_name;

I'll let you construct the string. The information seems more useful in separate columns.

Note the use of table aliases. This makes the query easier to write and read.

user_11440350

You can do conditional aggregation:

SELECT
    h.hotel_id,
    h.hotel_name,
    COUNT(r.room_no) || ' total rooms ' 
        || SUM(CASE WHEN r.room_no > 2 THEN 1 ELSE 0 END) 
        || ' have more than 2 bedrooms ' AS ACCOMODATION_AVAILABLE
FROM hotel h
INNER JOIN room r ON h.hotel_id = r.hotel_id
GROUP BY h.hotel_id, h.hotel_name;

Note: table aliases make the queries shorter and easier to understand. I modified your query to use them.

user_106767160
Related