From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: sum, min, max and null
Date: Mon, 18 Aug 2014 12:06:40 -0700 (PDT)
Message-ID: <1408388800913-5815291.post@n5.nabble.com> (raw)
In-Reply-To: <53F24431.9030801@shopzeus.com>
References: <53F24431.9030801@shopzeus.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: sum, min, max and null
In-Reply-To: <1408388800913-5815291.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox