Обсуждение: 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.