CODEWITHSUNDEEP
mysqldatabase
Roeland
Roeland

Reputation: 3858

Doing addition and comparing columns in WHERE statement

So I am trying to run a query which does some math in the WHERE statement. It seems that what I wrote is correct, yet it does not seem to work.

SELECT *
FROM table
WHERE col_a + col_b != col_d

Basically I want to see all records where col_a added to col_b does not add up to col_d.

Upvotes: 1

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I can think of three reasons why this might not seem to work.

The first is the most likely: that one or more of the values are NULL. To fix this, use COALESCE:

where coalesce(col_a, 0) + coalesce(col_b, 0) <> coalesce(col_d, 0)

Another reason would be that the results look to you like they are the same, but in the floating point presentation, they are really different. So, 0.999999999 + 5.0 might look like "6.0" but it is not equal to "6.0", because the difference is at a distant decimal point.

The third reason is arithmetic overflow or a typing problem (mixing tiny integers with floats or the like).

Upvotes: 2

Related Questions