Re: sum, min, max and null

[Matheus de Oliveira <matioli.matheus@xxxxxxxxx>]

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