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!