Page 1 of 1

Need sum if it is greater than zero, otherwise, return zero

Posted: Wed Nov 17, 2010 7:51 pm
by Luke
Hey guys, it's been quite a while! I've taken some time off work to focus on a personal project of mine and I have run into a bit of a problem. I have a table called users_points that keeps track of a user's points in my app. Depending on certain actions they take on the site, they can score points and they can get points taken away (score negative points). So my table is basically a list of user IDs and a total of points scored for any particular action. Something like:

Code: Select all

user_id, total
1, 2
1, -2
4, 1
3, 5
4, -2
5, 1
I need to sum up the total for each user, and if the sum is a positive number, then keep it, if it is a negative number, I need it to return zero. So with the above data I would need to return this:

Code: Select all

1, 0
4, 0
3, 5
5, 1
Does anybody know how I might accomplish this? Thanks in advance!

P.S. I am going to try to post regularly again now that I have the time. So expect to see quite a bit more of me!

Re: Need sum if it is greater than zero, otherwise, return z

Posted: Wed Nov 17, 2010 8:39 pm
by Christopher
Maybe something like:

SELECT user_id, IF(SUM(total)>0,SUM(total),0) FROM mytable GROUP BY user_id;

Re: Need sum if it is greater than zero, otherwise, return z

Posted: Thu Nov 18, 2010 1:47 am
by Weirdan
Or

Code: Select all

select
  user_id,
  greatest(sum(total), 0)
from theTable
group by user_id

Re: Need sum if it is greater than zero, otherwise, return z

Posted: Thu Nov 18, 2010 2:24 am
by Christopher
GREATEST() !!! I learn something for almost every Weirdan post. :)

Re: Need sum if it is greater than zero, otherwise, return z

Posted: Thu Nov 18, 2010 7:55 pm
by Luke
Genius! Thanks :)