Get value from column A associated with the maximum value of the sum of 2 other columns

user_12423432 0
Viewed 31

this is a SQL question. Sorry for the title, but I didn't know how to write it better.

I have 3 tables: room, checkin and checkout. Table room has as a column room_id. Table checkin has as columns: date, reservation_id, room_id. Table checkout has as columns: date, reservation_id, room_id.

I want to get only the room (its id) which has been slept on the most days (as in, SUM(checkout.date - checkin.date)).

I know only how to get it along with the amount of days. How can I only get one column (the room_id)?

My script for the 2 columns is like this:

SELECT r.room_id, SUM(c2.data - c1.data) FROM checkin c1
INNER JOIN checkout c2 ON c1.reservation_id = c2.reservation_id
INNER JOIN room r on r.room_id = c1.room_id
GROUP BY r.room_id
ORDER BY 2 DESC
FETCH FIRST ROW ONLY;

I think I'll need one subquery (at least), but I'm having trouble on doing this since I'm creating a new column.

sql
1 Answers

Just don't include the information you do not want in the SELECT:

SELECT ci.room_id
FROM checkin ci INNER JOIN
     checkout co
     ON ci.reservation_id = co.reservation_id
GROUP BY r.room_id
ORDER BY SUM(co.data - ci.data) DESC
FETCH FIRST ROW ONLY;

Note that room_id is in checkin, so the room table is not needed.

I do wonder if the JOIN should include both columsn:

     ON ci.reservation_id = co.reservation_id AND
        ci.room_id = co.room_id

This could be needed if different rooms on the same reservation had different check out dates -- and even without that, is probably helpful for performance reasons.

user_11440350
Related