On Mon, Aug 18, 2014 at 3:21 PM, Nagy László Zsolt <gandalf@shopzeus.com> wrote:
However, I'm in need of a different interpretation, where sum() should change the output value to null if there is any NULL value in the input. How do I achieve this? I was experimenting with this:
select case when sum(hasnull)>0 then null else sum(v) end as mysum from ( select v, case when v is null then 1 else 0 end as hasnull from ( select 1 as v union all select null union all select 3 ) s ) s2
One approach is simple using "count(*)" that does take NULL into account:
SELECT CASE WHEN count(v)=count(*) THEN sum(v) END AS mysum ...
Perhaps it would be better in terms of performance to check for NULLs first.
Regards,
-- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres