CODEWITHSUNDEEP
phpmysql
user1470957
user1470957

Reputation: 461

MYSQL select, set all negative values to zero

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

Answers (3)

RightClick
RightClick

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

d4Rk
d4Rk

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

Alexandre
Alexandre

Reputation: 302

select case when test.A - test.B >= 0 then test.A - test.B else 0 end as myResult from test;

Upvotes: 1

Related Questions