Re: CASE/WHEN behavior with NULLS

David Johnston <polobo(at)yahoo(dot)com> writes:
> On Aug 31, 2012, at 21:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> David Johnston <polobo(at)yahoo(dot)com> writes:
>>> That said you might want to try
>>> SUM(COALESCE(foo, 0))

>> Actually I'd go with "COALESCE(SUM(foo), 0)" since that requires only
>> one COALESCE operation, not one per row.

> These are not equivalent if some values of foo are not-null and you want the sum of all non-null values while replacing any nulls with zero. So the decision depends on what and why you are summing.

But SUM() ignores input nulls, so I think they really are equivalent.
I agree that in a lot of other cases (for instance MAX), you'd have to
think harder about which behavior you wanted.

The key point here is that whatever is inside the aggregate function
call is computed once per row, and then the aggregate is applied to
those results, and then whatever is outside the aggregate is done once
on the aggregate's result. SQL's syntax doesn't make this too obvious,
but you really have to grasp that to make any sense of what's happening.

regards, tom lane