Re: sum, min, max and null

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.