Question 21820 in Mysql Question posted in
The official MySQL documentation can be found here.

SELECT name from table WHERE scalar subquery is true – Mysql

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.

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Here is another solution:

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

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search