trying to get the rooms of hotels that got the mos

Question

I'm trying to implement a query which give me the sum of most profitable room in each hotel(25 hotels)

Below is my query:

SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_number,sum(rooms.room_price) AS profit,COUNT(rooms.room_id) AS count FROM hotels,rooms,bookings WHERE hotels.hotel_id=rooms.hotel_id AND rooms.room_id=bookings.room_id GROUP BY rooms.room_id

and this is the closest outcome i got.. ignore the hotel name language

This is the outcome that I've reached so far,

hotels

rooms

bookings p.1

bookings p.2 (remaining records)

hotel_id 1 has 5 rooms, the room_number 300 made the most profit. I want to show the most profit only of each hotel. I don't need the other rooms that made less profit.

---

Update: So i solved a similar query where i want to get the best 2 rooms that made the most profit. But, i just can't think of any function that can give me only best profit of each hotel. little hint or help would be appriciated

Answer 1

Try this query:

SELECT * FROM 
(SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_number,SUM(rooms.room_price) AS profit,COUNT(rooms.room_id) AS COUNT
FROM hotels,rooms,bookings
WHERE hotels.hotel_id=rooms.hotel_id
AND rooms.room_id=bookings.room_id
GROUP BY rooms.room_id) a GROUP BY hotel_id;

Edit:

This might do it:

SELECT hotel_id,room_id,room_number,MAX(a.tc) AS "Count",MAX(tp) AS "MostProfit" FROM
(SELECT hotel_id,rooms.room_id,room_number,COUNT(rooms.room_id) AS "tc",SUM(room_price) AS "tp" FROM rooms JOIN bookings 
ON rooms.room_id=bookings.room_id
GROUP BY rooms.room_id) a GROUP BY hotel_id

Answer 2

Please try below once:

SELECT RO_BOOK.HOTEL_ID,
       RO_BOOK.ROOM_ID,
       RO_BOOK.ROOM_NUMBER,
       RO_BOOK.TOTAL_BOOKINGS,
       MAX(RO_BOOK.TOTAL_EARNINGS) PROFITS
FROM(
    SELECT  ROOMS.HOTEL_ID
        ROOMS.ROOM_ID,
        ROOMS.ROOM_NUMBER,
        COUNT(ROOMS.ROOM_ID) TOTAL_BOOKINGS
        SUM(ROOMS.ROOM_PRICE) TOTAL_EARNINGS
    FROM 
        ROOMS, BOOKINGS
    WHERE 
        BOOKINGS.ROOM_ID = ROOMS.ROOM_ID
        GROUP BY ROOMS.ROOM_ID) RO_BOOK
GROUP BY RO_BOOK.HOTEL_ID ;

It is similar to @tcadidot0 code, but column MAX(a.tc) AS "Count" return the maximum count irrespective of ROOM_ID. For eg: if hotel 1 has 2 rooms say, R100 and R200. The Cost of R100 be 1000 and R200 be 100. No of times R100 booked be 1, and R200 be 3. So the query would return: HOTEL 1 , R100, COUNT 2, PROFIT 1000.

Please correct me if I got the question wrong.