David Johnston <polobo@xxxxxxxxx> writes: > On Aug 31, 2012, at 21:52, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> David Johnston <polobo@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
