Reputation: 461
Let assume I have mysql table called "test" in this table I have two columns "A", "B". I want to get the result of A-B, but for each row that the result is negative, I want to set it to zero.
So I can write : select test.A - test.B as myResult
But I don't know how to set myResult to zero when myResult is negative number. Please help Thanks
Upvotes: 1
Views: 4121
Reputation: 1112
Use the IF
statement to set it to zero when it's negative.
SELECT a, b, IF(a-b<0,0,a-b) as myResult FROM test;
This only does that to your query results, it doesn't actually UPDATE
the table.
Upvotes: 5
Reputation: 6693
Why always making things complicated?
Just use the built in functions for such issues:
select greatest(0, test.A-test.B) as myResult from test;
From the docs: http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#function_greatest
Upvotes: 7
Reputation: 302
select case when test.A - test.B >= 0 then test.A - test.B else 0 end as myResult from test;
Upvotes: 1