Set -1 to 0 while adding in MySQL

Question

I've got a database using DECIMAL type values for various tests on crude oil. When running these tests you run into certain cases: 1.) Test was run and value is found; 2.) You can definitively say nothing is there (0 in database); 3.) Test was not run (NULL in database); 4.) There is something found, but testing is not accurate enough to discern exactly how much (-1 is used in the database for this case).

My problem here is that I need to return result sets as a sum of all 4. I have been using a "SELECT property1+property1+... FROM..." type of query to do this. I use 'WHERE _ IS NOT NULL' to deal with case 3, but the problem is in case 4, I don't want to add -1, I want to add it as 0. Is there any way I can do this with MySQL? I'd really rather not get each result set separately and have to use PHP to deal with this, but I suppose I can if there is no way around it.

Also, before someone recommends it, there isn't too much I can do in the way of changing the database structure. However, if you have some completely ingenious fix that requires that, I wouldn't be opposed to hearing it regardless.

EDIT: Thanks for all the quick replies everybody. Appreciate the help!

Answer 1

if you do something like ... + cond4 + ... you can replace it with ... + CASE WHEN cond4 == -1 THEN 0 ELSE cond4 END + ...

Answer 2

You could use the if statement:

property1 + property2 + property3 + IF(property4 > 0, property4, 0)

Answer 3

SELECT  SUM(GREATEST(COALESCE(property1, 0), 0))
FROM    mytable

Answer 4

I would consider the Control Flow Functions

SELECT property1 + property2 + property3 + IF(property4 > 0, property4, 0) WHERE _ IS NOT NULL

Answer 5

I personally wouldn't have this kind of logic executed on the database, it doesn't seem right.