Message-ID: <53F24431.9030801@shopzeus.com>
Date: Mon, 18 Aug 2014 20:21:37 +0200
From: =?ISO-8859-2?Q?Nagy_L=E1szl=F3_Zsolt?= <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: sum, min, max and null
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin