CODEWITHSUNDEEP
phpmysql
puks1978
puks1978

Reputation: 3697

Get lowest sum and highest sum of users from MySQL

I have a MySQL database that is holding users activities on the site. Each time the user completes something I log the amount of time it took them to complete. The field is stored as a decimal.

I am wanting to know how I can get the lowest sum and the highest sum amounts of users. Lets say user1 has performed 2 tasks taking .5 each. Their sum would be 1.0. User2 has performed 10 tasks each taking 1.5 each. Their sum would be 15. User3 has performed 20 tasks each taking .25 each. Their sum would be 5.

So running a query over the DB the lowest amount would be 1 and the highest amount would be 15.

I know how to get the sum of columns but not sure how to return the lowest and the highest.

Thanks.

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You can do this with a subquery and two levels of aggregation:

select min(totaltime), max(totaltime)
from (select user, sum(amountoftime) as totaltime
      from t
      group by user
     ) t;

Actually getting the users associated with the min and max is a bit more difficult, but that is not this question.

Upvotes: 0

Related Questions