SELECT name from table WHERE scalar subquery is true-CodePudding

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.

CodePudding user response：

you need only a little to rewrite it

CREATE TABLE customer(customer_id int ,first_name varchar(100) ,last_name varchar(100))

CREATE TABLE payment (customer_id int , amount decimal(10,2))

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

customer

fiddle

CodePudding user response：

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.

CodePudding user response：

Is this the subquery you're looking for?

Sub-query: get the top 1 customer_id with most payment amount
Outer-query: get the customer's full name

with cte_top1_customer as (
select customer_id 
  from payment 
 group by customer_id 
 order by sum(amount) desc 
 limit 1)
select concat(c.first_name, ' ', c.last_name) as customer
  from customer c
  join cte_top1_customer t
 using (customer_id);