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.

[Join vs. sub-query](https://stackoverflow.com/q/2577174/3404097) — philipxy, Dec 11 '22 at 05:46

score 2 · Accepted Answer · answered Dec 10 '22 at 02:31

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.

Your method would be the most accurate for what I was looking for as far as using a scalar subquery! — UnclePete, Dec 10 '22 at 21:53

score 1 · Answer 2 · answered Dec 10 '22 at 02:02

1

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

answered Dec 10 '22 at 02:02

nbk

45,398
8
30
47

THAT WORKS!! I also came up with this 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; – UnclePete Dec 10 '22 at 21:47
The way you did it technically would not constitute as a scalar subquery. – UnclePete Dec 10 '22 at 21:52
technacally ture , but thea ccepted answer, would give you multiple rows as result mine not – nbk Dec 10 '22 at 21:58

score 0 · Answer 3 · answered Dec 10 '22 at 05:33

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);

score 0 · Answer 4 · answered Dec 10 '22 at 21:49

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;

SELECT name from table WHERE scalar subquery is true

4 Answers4