SUM (Only SUM positive Records) 1

[bgarlock]

I have an application, which is broken with respect to recording usage. If the emergency stop is pressed, the application still records the usage, but because it interfaces with a scale, and the weight of the scale usually contains a bucket, the usage recorded will be -2 to -5 (if the bucket is removed). Since users can press the stop several times a day, there are plenty of negative usage values in the DB.

I would like to only SELECT when the weight is > 0. This is easy with a "regular" SELECT statement. However, I am trying to use the SUM feature, to give the report a summary, and currently the report takes into account the negative values. How do I only SELECT based on the weights > 0 in my SUM??

TIA... Bruce Garlock
bruceg@tiac.net

http://brucegarlock.com

 

[jimbopalmer]

select sum(myvalue) from mytable where myvalue > 0; will work


select sum(decode(sign(myvalue),-1,0,0,0,1,myvalue)) from mytable; also works


the sign only returns -1 0 and 1 so the decode says if it is -1 return a zero, if it is 0 return a zero and if it is a 1 return myvalue, lastly we sum all those. I tried to remain child-like, all I acheived was childish.

 

[jimbopalmer]

decode may be unique to oracle's DBMS, I have not used any others, the first solution should be generic. I tried to remain child-like, all I acheived was childish.

 

[bgarlock]

Option 1 does the trick. Thanks Bruce Garlock
bruceg@tiac.net

http://brucegarlock.com