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.
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.
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
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
SELECT name, SUM(amount) as total FROM user_amounts GROUP BY name ORDER BY total SUM(Marks) DESC LIMIT 1
