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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
