Date: Mon, 18 Aug 2014 12:06:40 -0700 (PDT)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-admin@postgresql.org
Message-ID: <1408388800913-5815291.post@n5.nabble.com>
In-Reply-To: <53F24431.9030801@shopzeus.com>
References: <53F24431.9030801@shopzeus.com>
Subject: Re: sum, min, max and null
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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