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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

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

Post 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!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

Maybe something like:

SELECT user_id, IF(SUM(total)>0,SUM(total),0) FROM mytable GROUP BY user_id;
(#10850)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post by Weirdan »

Or

Code: Select all

select
  user_id,
  greatest(sum(total), 0)
from theTable
group by user_id
Last edited by Weirdan on Thu Nov 18, 2010 4:01 am, edited 1 time in total.
Reason: group be -> group by
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

GREATEST() !!! I learn something for almost every Weirdan post. :)
(#10850)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

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

Post by Luke »

Genius! Thanks :)
Post Reply