Re: sum, min, max and null

On Mon, Aug 18, 2014 at 3:21 PM, Nagy László Zsolt <gandalf(at)shopzeus(dot)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