CODEWITHSUNDEEP
mysql
tadpole
tadpole

Reputation: 1219

How to form sql structure to get max total value

I have a table "sales"

transactionId  salepersonId amount
1                1          100
2                1          200
3                2          50
4                3          60
5                3          200

I like to find out how to get the salesperson with the highest total sale. I know I need to use max and sum but I don't know how to combine them. This is what I got

   select salespersonId, sum(amount) from sales group by salesperson;

This would give me the total sales of each person, but I am not sure the next step to get the max. can someone help me ?

Upvotes: 0

Views: 77

Answers (3)

Abdul Rehman
Abdul Rehman

Reputation: 1794

This should work

select salepersonId, sum(amount) from sales group by amount DESC limit 1

OR

select rec.id, max(rec.amt) from (select salepersonId id, sum(amount) amt from sales group by salepersonId) AS rec

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

The standard SQL way is to use order by and limit or top

select salespersonid, sum(amount) as total
from sales
group by salespersonid
order by total desc
limit 1;

In SQL Server, you would use top 1 instead of limit 1:

select top 1 salespersonid, sum(amount) as total
from sales
group by salespersonid
order by total desc;

Upvotes: 1

Dan
Dan

Reputation: 11104

select salespersonId, sum(amount) as total 
from sales group by salespersonID ORDER BY total DESC LIMIT 1;

Upvotes: 0

Related Questions