removing all 0 value rows returns different result?

Question

Hi there.

this query SELECT SUM(s.msg_sent_datetime - r.date_received) AS difference

returns -743726381625992.000000

but if i add on WHERE (s.msg_sent_datetime - r.date_received) > 0

it returns: 343435413.000000

I thought the 0 value rows would have no effect on a sum? also, what is up with the .000000

Answer 1

Your conditional statement also ignores negative values i.e. if (s.msg_sent_datetime - r.date_received) < 0, the row is ignored. On the other hand, if that condition didn't exist (like in your original query), you're also adding the negative numbers.

Answer 2

This is what you want WHERE (s.msg_sent_datetime - r.date_received) <> 0

That is assuming you want negative results as well, which based on the field names should be the majority.