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
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 |