david.jack (10/27/2016)
Hi,I'm very grateful for any light that can be shed on this. My code was per the following:
SUM( ISNULL( NULLIF(VAL1,0), VAL2) )
My understanding is that this should return the SUM of (VAL1 if it's not 0, otherwise VAL2). However what we're getting if VAL1 is not 0, is VAL1 + VAL2!?
I've got the correct values coming out with the following:
ISNULL( NULLIF( SUM(VAL1),0), SUM(VAL2) )
I really just want to understand what's happening here because until now, I thought I did :ermm:
I know this problem is already solved, but when I read the various comments, it wasn't clear to me that anyone correctly assessed the original issue. If I correctly understand the problem, the objective is to SUM the VAL2 column only when the SUM of the VAL1 column is zero, which is what the poster effectively said by presenting the second set of code that produces the correct result. To explain the WHY behind it is fairly simple. When you use SUM, whatever expression appears as the argument to that function is evaluated for every record returned by the FROM and WHERE clauses, and only then, is summed. The incorrect value returned by the posters' first set of code was the sum of the individual values of the original expression that appeared inside the function. The correct value returned by the posters' second set of code performed TWO sums, and only then compared the SUM of VAL1 to 0, and nulled it out in favor of the SUM of VAL2.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)