CODEWITHSUNDEEP
mysql
user3789191
user3789191

Reputation: 97

How to Sum the amount and query the highest amount user in mysql

I have the record in table as follow;

no. name  amount
1.  joh   100
2.  cathy 100
3.  phyo  300
4.  phyo  100
5.  joh   100
6.  cathy 50

I would like to sum the amount of each name and query the highest user with amount. How to make these query in mysql.

Upvotes: 0

Views: 79

Answers (4)

Sal00m
Sal00m

Reputation: 2916

This query should work:

SELECT name, SUM(amount) as total FROM user_amounts GROUP BY name ORDER BY total DESC LIMIT 1

Explanation:

I'll explain how you should write your queries, or at least how to solve next time.

First start with a simple select of names and amounts:

SELECT name, amount FROM user_amounts

Then we want to know amount of every name, so we have this query:

SELECT name, SUM(amount) as total FROM user_amounts GROUP BY name

As you can see i used SUM function and GROUP BY because if you don't use GROUP BY name then your result will be only one row.

After that we have something like this

joh   200
cathy 150
phyo  400

But we want the first the higher ammouunt, so need to order it, and we only want the higher, so limit 1 to only recover the first name

SELECT name, SUM(amount) as total FROM user_amounts GROUP BY name ORDER BY total DESC LIMIT 1

Hope it'll be clear.

Upvotes: 1

Jobayer
Jobayer

Reputation: 1231

You have two parts - 1) At first need to sum individual user data 2) find out highest data. check this question with answer SQL query to sum the data, you will get idea for doing it. use select name, sum(amount) as total from table group by name order by total DESC

Upvotes: 0

Choco
Choco

Reputation: 1064

SELECT name, SUM(amount) as total FROM user_amounts GROUP BY name ORDER BY total SUM(Marks) DESC LIMIT 1

Upvotes: 0

NMK
NMK

Reputation: 1020

Try this. But this will have issues with you have a tie.

SELECT name, SUM(amount) as total
 FROM user_amounts 
GROUP BY name ORDER BY 2 desc LIMIT 1

Upvotes: 0

Related Questions