-1

I need to get which guest spent the most in a hotel throughout their lifetime. The room table has the price per individual room.

SELECT g.guest_id, g.name, g.email, sum(room_price) sumcost
FROM booking b
INNER JOIN guest g ON b.guest_id = g.guest_id
INNER JOIN room r ON r.room_id = b.room_id
GROUP BY g.guest_id;

The above query gives me a list of guests and gets me their sum they spent. Now I need to get only the guest who has the maximum sumcost instead of getting the whole list of all the guests. How can I accomplish this?

craftdeer
  • 985
  • 5
  • 20
  • 36
  • 1
    order by & limit 1? – HTMHell May 01 '22 at 19:27
  • 2
    What `MySQL` version are you using? And please add table description , data examples and expected result always when asking a sql question all in text format. You can apply an outer query , you can use limit ...etc. What happen if two guests have the same max sumcost? – Ergest Basha May 01 '22 at 19:28
  • There may be more than 1 guest with the same maximum amount spent. Mysql version is 8.0.26 – craftdeer May 01 '22 at 19:35
  • @ErgestBasha, I cannot use a limit since there are more than 1 result. How do I implement an outer query? – craftdeer May 01 '22 at 19:56
  • Does this answer your question? [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – kmoser May 01 '22 at 20:02
  • @craftdeer you can use recursive cte like in this example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=04e49734901ae22d7fe4ad8ca7be2b74 , but you haven't updated the question with the needed information to solve the problem, so I only can guess – Ergest Basha May 02 '22 at 07:56

2 Answers2

1
SELECT
   s.ggid,
   s.sumcost
FROM (SELECT 
         g.guest_id ggid, 
         sum(room_price) sumcost
         FROM booking b
         INNER JOIN guest g ON b.guest_id = g.guest_id
         INNER JOIN room r ON r.room_id = b.room_id
         GROUP BY g.guest_id
         ORDER BY sumcost  DESC) s
WHERE s.sumcost = ( SELECT sum(room_price) sumcost
                    FROM booking b
                    INNER JOIN guest g ON b.guest_id = g.guest_id
                    INNER JOIN room r ON r.room_id = b.room_id
                    GROUP BY g.guest_id
                    ORDER BY sumcost DESC
                    LIMIT 1
                   )
Paul Neralich
  • 76
  • 7
  • 1
    Thank. But there might be more than 1 guest with the same max amount spent. Is there any way to handle that? – craftdeer May 01 '22 at 19:38
  • 1
    @craftdeer I just edited the ORDER BY to handle that possibility. Can you try it out on a known dataset with more than one guest with the same sumcost value? – Paul Neralich May 01 '22 at 19:43
  • It works. But there are 3 people with the same `sumnum`. There may be more in the future. I am wondering if we can use the MAX function somehow – craftdeer May 01 '22 at 19:46
  • 1
    @craftdeer, I understand, I don't think my solution will handle that. I'm thinking we may need to do a subquery with a MAX function, as you suggest, in the outer query. – Paul Neralich May 01 '22 at 19:54
  • @craftdeer, I've completely rewritten this with subqueries to handle the situation where there are multiple guests with the same max amount spent. However, I'm not sure this is optimized. Perhaps other experts can improve this to be simpler and faster. – Paul Neralich May 01 '22 at 20:51
1

You can use a window function, e.g. MAX OVER:

SELECT g.guest_id, g.name, g.email, sums.sumcost
FROM guest g
JOIN
(
  SELECT
    b.guest_id, SUM(r.room_price) AS sumcost,
    MAX(SUM(r.room_price)) OVER () AS maxsumcost
  FROM booking b
  INNER JOIN room r ON r.room_id = b.room_id
  GROUP BY b.guest_id
) sums ON sums.guest_id = g.guest_id AND sums.sumcost = maxsumcost;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73