sum, min, max and null

From: Nagy László Zsolt <gandalf(at)shopzeus(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: sum, min, max and null
Date: 2014-08-18 18:21:37
Message-ID: 53F24431.9030801@shopzeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I understand that sum, min and max ignore null values. So for example
this query:

select sum(v),min(v) from (
select 1 as v
union all select null
union all select 3
) s

will result in sum(v)=4 , min(v)=1.

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

It works but this is so ugly that I'm not even trying to use it in
production code. Is there any better way to do it?

Thanks,

Laszlo

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Craig James 2014-08-18 18:59:49 pg_upgrade FAIL: can't find tablespaces
Previous Message liuyuanyuan 2014-08-18 04:02:07 OF TYPE without SCHEMA specified in TABLE creation interface