-2

I've been tasked with finding the client with the highest sum of payments, the table they gave me contains the customer number and amount, I have not been able to do this,

SELECT suma.customerNumber, suma.Tot
FROM (
    SELECT DISTINCT payments.customerNumber, ROUND(SUM(payments.amount), 2) AS Tot
    FROM payments
    GROUP BY customerNumber
    ORDER BY Tot DESC
) suma
WHERE suma.Tot = (SELECT MAX(Tot) FROM suma)
GROUP BY suma.customerNumber;

This is an example of the kind of code I've been writing to solve this, I know I can't just do MAX(SUM(blah)), but that's basically what I need to do, and I need help.

Improve this question
1
  • 1
    You've tagged MySQL but you've said in a comment below that SELECT TOP 1 ... works for you... which is not the case for MySQL. You need to update your question to reflect the correct RDBMS you are using. And with any limitations given what you have learned in your course. Commented yesterday

1 Answer 1

Reset to default
2

I think I would need some clarification on the question. You have many clients and payment amounts on different dates I am assuming.

If you are being tasked to only show the customer that has made the greatest combined amount of payments. It seems easy if it is just one source and few columns, just group the data, order by highest SUM of payment amounts and then just Limit the result to 1, which is the highest or most payment amount.

If the result needs clean data, then you can show the value with DECIMAL(18,2) to only show 2 decimals.

SELECT
    payments.customerNumber
    , Tot = SUM(CONVERT(DECIMAL(18,2), payments.amount))
FROM payments 
GROUP BY payments.customerNumber
ORDER BY Tot DESC
LIMIT 1
Improve this answer
New contributor
Dobrovodsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
Sign up to request clarification or add additional context in comments.

7 Comments

More or less, this query is for a test so we are not allowed to use things we haven't seen in class, LIMIT is one of the things we haven't seen, so yeah, it works, but not how they ask me to, but thanks nonetheless
Ok, if that is the case, then instead of LIMIT, I would use a SELECT TOP 1 at the beginning and that will filter or limit the results to only the TOP values by your given ORDER BY clause.
yeah, that works, thank you very much.
Mysql does not support the top clause, it supports the limit clause instead
Thats not possible for MySQL... so if it works then the incorrect RDBMS has been specified.
Then you need to state that in the question up front before people start trying to find a solution for you.
With LIMIT 1 you return 1 random customerNumber among all having the greatest SUM … in theory there is no reason you always have only 1.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.