Обсуждение: sum, min, max and null
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
Laszlo Nagy wrote > 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 I believe you will have to write a custom CREATE FUNCTION and CREATE AGGREGATE where the relevant state transitions functions become NULL as soon as one the incoming input is null. The problem for min/max will be distinguishing between the original null and a null as a result of a prior null input. You might be forced to use min/max integer for the staring value instead and error if an input happen to be the same. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/sum-min-max-and-null-tp5815288p5815291.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
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 ...
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
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres