Re: CASE/WHEN behavior with NULLS

On 2012-09-01, Chris Angelico <rosuav(at)gmail(dot)com> wrote:
> On Sat, Sep 1, 2012 at 12:07 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>> 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.
>
> It comes to the same result with SUM though isn't it?

no

sum over zero rows always returns null.
an external coalesce will make the result 0 whilst an internal coalesce
will have no effect and NULL will be the result.

with t as ( select 1::int as a where false )
select sum(coalesce (a,0)) as inner,coalesce (sum(a),0) as outer from t;

--
⚂⚃ 100% natural