imin imin - 5 months ago 11
MySQL Question

Get only the highest value returned by Group By

Here's how my table (simplified) look like

agentID|amount
1 | 5
2 | 2
3 | 4
3 | 2
1 | 3


Now if I do

SELECT agentID, SUM(amount) AS totalAmount FROM myTable GROUP BY agentID


I'd get the sum of amount for each agentID. But what if I'm only interested in the returned agentID that has the biggest sum(amount)?

Tried something like

SELECT agentID, MAX(SUM(amount) AS Bought) FROM myTable GROUP BY agentID


OR

SELECT MAX(SELECT agentID, SUM(amount) AS Bought FROM myTable GROUP BY agentID) FROM myTable


but it returns syntax error :p

Answer

YOU CAN ADD ORDER BY AT THE END

SELECT agentID, SUM(amount) AS totalAmount FROM myTable GROUP BY agentID ORDER BY SUM(amount) DESC LIMIT 1;