CODEWITHSUNDEEP
mysqlsql
Splendid
Splendid

Reputation: 1357

MySQL - max in group of results

So maybe my question title is not most relevant but I'm not good in sql and I will need to learn it for sure (So hint for some book would be very useful)

Anyhow I got table which has following (important) columns:

user_id,username,revenue

and records like this:

1,john,39
2,mark,53
3,philipp,7
1,john,17
2,mark,3
2,mark,100

I need to get user who has the biggest revenue, so I need to get all rows, calculate total earnings and print the user who has the biggest revenue in total.

Any hints ;) ?

Upvotes: 0

Views: 77

Answers (1)

BMN
BMN

Reputation: 8508

You can use ORDER BY and LIMIT :

SELECT user_id, username, SUM(revenue) FROM table
GROUP BY user_id /* add ', username' ONLY if two different usernames with the same user_id is a possible case */
ORDER BY SUM(revenue) DESC
LIMIT 0,1

With this, you'll calculate the revenue for each user, you order by decreasing total and get only the first one, so that you have the highest revenue.

Upvotes: 3

Related Questions