SELECT name from table WHERE scalar subquery is true

Question

How could I convert this:

SELECT CONCAT (c.first_name, ' ', c.last_name) AS customer,
    SUM(CAST(p.amount as float)) AS total_amount 
FROM customer c
INNER JOIN payment p ON c.customer_id=p.customer_id
GROUP BY c.customer_id 
ORDER BY total_amount desc 
LIMIT 1;

Into a subquery that says something along the lines of:

SELECT CONCAT (c.first_name, ' ', c.last_name) AS customer
FROM customer
WHERE

And the WHERE would run the SUM(CAST(p,amount as float etc.

I want a single name. The customer who has payed the most. So the max of all the sums.

Answer 1

You can use a subquery in the WHERE clause to find the maximum total amount from the first query, and then use that in the WHERE clause of the outer query to filter for the customer who has paid the most:

SELECT CONCAT (c.first_name, ' ', c.last_name) AS customer
FROM customer c
WHERE (SELECT SUM(CAST(p.amount as float))
       FROM payment p
       WHERE c.customer_id = p.customer_id
      ) = (SELECT MAX(total_amount)
            FROM (SELECT SUM(CAST(p.amount as float)) AS total_amount
                  FROM customer c
                  INNER JOIN payment p ON c.customer_id = p.customer_id
                  GROUP BY c.customer_id
                 ) max_amounts
           )

This works by first finding the maximum total amount using a subquery in the WHERE clause of the outer query. Then, the outer query filters for the customer who has paid the most by matching the total amount for each customer to the maximum total amount found by the subquery.