On 2012-09-01, Chris Angelico <rosuav@gmail.com> wrote:
> On Sat, Sep 1, 2012 at 12:07 PM, David Johnston <polobo@yahoo.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
replacingany 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