0

I need to find user who has the most to pay for reservations, and if there are 2 or more users with same amount to show all of them, so I need MAX of SUM.

My table reservation shortened looks like this:

reservation_id, user_id, performance_id, amount_to_pay, date

So I ahve this code

SELECT user_id, SUM(amount_to_pay) FROM reservation GROUP BY user_id

And I got

User 1 - 9000
User 2 - 9000
User 3 - 5000

It needs to show User 1 and User 2 with 9000.

Nick
  • 138,499
  • 22
  • 57
  • 95
1005
  • 13
  • 1
  • Possible duplicate of [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) – Nick Jan 29 '19 at 03:46

1 Answers1

0

One solution is to use the HAVING clause with a correlated subquery that gets the max value between all the sums and restrict the rows to those whose SUM(amount_to_pay) equals the max_value.

SELECT
   user_id,
   SUM(amount_to_pay) AS total
FROM
   reservation AS r
GROUP BY
   user_id
HAVING
   total = (SELECT SUM(amount_to_pay) AS tot
            FROM reservation
            GROUP BY user_id
            ORDER BY tot DESC LIMIT 1)

Online example: DB-Fiddle

UPDATE POST COMMENTARY:

For sum only the active reservations you could take one of the next approaches:

A) Add the restriction on outer query and subquery:

SELECT
   user_id,
   SUM(amount_to_pay) AS total
FROM
   reservation AS r
WHERE
   status = "active"
GROUP BY
   user_id
HAVING
   total = (SELECT SUM(amount_to_pay) AS tot
            FROM reservation
            WHERE status = "active"
            GROUP BY user_id
            ORDER BY tot DESC LIMIT 1)

B) Use CASE WHEN ... END inside the SUM() method:

SELECT
   user_id,
   SUM(CASE WHEN status = "active" THEN amount_to_pay END) AS total
FROM
   reservation AS r
GROUP BY
   user_id
HAVING
   total = (SELECT SUM(CASE WHEN status = "active" THEN amount_to_pay END) AS tot
            FROM reservation
            GROUP BY user_id
            ORDER BY tot DESC LIMIT 1)

Online example: DB-Fiddle

Community
  • 1
  • 1
Shidersz
  • 16,846
  • 2
  • 23
  • 48
  • This is ok, but now I have one little problem. In my reservation I have status (active/inactive) and I need to SUM only active reservations. I've tried this but it doesn't work: SELECT user_id, SUM(amount_to_pay) AS total FROM reservation AS r GROUP BY user_id HAVING total = (SELECT SUM(amount_to_pay) AS tot FROM reservation WHERE status = "active" GROUP BY user_id ORDER BY tot DESC LIMIT 1) and also status = "active" before GROUP BY user_id in first query. – 1005 Jan 29 '19 at 20:28
  • Everything is fine, thank you so much for quick and accurate answer. – 1005 Jan 29 '19 at 23:28
  • @1005 you are welcome, consider to upvote my answer when you gain enough reputation – Shidersz Jan 30 '19 at 02:20