pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

sum, min, max and null
3+ messages / 3 participants
[nested] [flat]

* sum, min, max and null
@ 2014-08-18 18:21  Nagy László Zsolt <gandalf@shopzeus.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Nagy László Zsolt @ 2014-08-18 18:21 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: sum, min, max and null
@ 2014-08-18 19:06  David G Johnston <david.g.johnston@gmail.com>
  parent: Nagy László Zsolt <gandalf@shopzeus.com>
  1 sibling, 0 replies; 3+ messages in thread

From: David G Johnston @ 2014-08-18 19:06 UTC (permalink / raw)
  To: pgsql-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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: sum, min, max and null
@ 2014-08-18 19:13  Matheus de Oliveira <matioli.matheus@gmail.com>
  parent: Nagy László Zsolt <gandalf@shopzeus.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Matheus de Oliveira @ 2014-08-18 19:13 UTC (permalink / raw)
  To: Nagy László Zsolt <gandalf@shopzeus.com>; +Cc: pgsql-admin

On Mon, Aug 18, 2014 at 3:21 PM, Nagy László Zsolt <gandalf@shopzeus.com>
wrote:

> 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
>


One approach is simple using "count(*)" that does take NULL into account:

     SELECT CASE WHEN count(v)=count(*) THEN sum(v) END AS mysum ...

Perhaps it would be better in terms of performance to check for NULLs first.

Regards,
-- 
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2014-08-18 19:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-18 18:21 sum, min, max and null Nagy László Zsolt <gandalf@shopzeus.com>
2014-08-18 19:06 ` David G Johnston <david.g.johnston@gmail.com>
2014-08-18 19:13 ` Matheus de Oliveira <matioli.matheus@gmail.com>

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