0

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

Aymsul
  • 1
  • 3
  • Try editing your grouping to `GROUP BY hotels.hotels_id,rooms.room_id` and see if that's what you want – FanoFN Mar 14 '19 at 04:37
  • @tcadidot0 tried `GROUP BY hotels.hotels_id` and that gave me the sum of all of them "4500+1200+2500+...". But, what i need is to ignore all the profits and only take the 4500 because i only want the highest. I also tried `GROUP BY hotels.hotels_id,rooms.room_id` writing it all gave me the same out come i had before – Aymsul Mar 14 '19 at 04:45
  • My bad @Aymsul. I'll take a look again at your sample data and try to give a valid answer. – FanoFN Mar 14 '19 at 05:04

2 Answers2

0

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.

Shanmukha Reddy
  • 81
  • 1
  • 10
0

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
FanoFN
  • 6,815
  • 2
  • 13
  • 33
  • This code is almost perfect except it only get the first record of each hotel , not the highest profit tried doing order by but it's pointless . thank you for trying to help, i'll try to edit the code u gave me after work. if i find a solution i'll post it here – Aymsul Mar 14 '19 at 05:37
  • I figured the same @Aymsul . I'm still working on to fulfill your condition. If I'm able, I'll update my answer as well. – FanoFN Mar 14 '19 at 06:21
  • but could you explain why you used double select and `GROUP BY rooms.room_id) a GROUP BY hotel_id` and this two group by , i just don't get it. Never used a query like that – Aymsul Mar 14 '19 at 15:46
  • The query inside `(SELECT hotel_id,rooms.room_id,room_number,COUNT(rooms.room_id) AS "tc" ... ) a` is called a subquery. This subquery I return the `COUNT(rooms.room_id) AS "tc",SUM(room_price) AS "tp"` value by room, hence the `... GROUP BY rooms.room_id) a ` is used in the subquery. Then I write another query to return maximum `tc` and `tp` value `GROUP BY hotel_id` from the subquery itself. – FanoFN Mar 15 '19 at 00:22
  • To make it simple to understand, the second query (outer query) is only there to specifically return what is the maximum value of your initial query [ `sum(rooms.room_price) AS profit,COUNT(rooms.room_id) AS count` ] value. – FanoFN Mar 15 '19 at 00:25