Thread: Aggregates with NaN values

I am happy to see NaN and infinity handled in input.  I would now like
to compute aggregates (avg, min, max, etc) on columns with NaN values
in them.  The standard behavior (it appears) is to have the aggregate
return NaN if the data contain one-or-more NaN values.  I am used to
using coalesce with NULL values, but that doesn't work with NaN.  I
can deal with these using CASE statuement to assign a value, but is
there a standard way of dealing with the NaN (or Infinity, for that
matter) cases to get a behvavior where they are "ignored" by an
aggregate?

Thanks,
Sean

On Thu, 2008-12-04 at 13:01 -0500, Sean Davis wrote:
> I am happy to see NaN and infinity handled in input.  I would now like
> to compute aggregates (avg, min, max, etc) on columns with NaN values
> in them.  The standard behavior (it appears) is to have the aggregate
> return NaN if the data contain one-or-more NaN values.  I am used to
> using coalesce with NULL values, but that doesn't work with NaN.  I
> can deal with these using CASE statuement to assign a value, but is
> there a standard way of dealing with the NaN (or Infinity, for that
> matter) cases to get a behvavior where they are "ignored" by an
> aggregate?
> 
> Thanks,
> Sean
> 

Have you considered using a where clause?

-Mark

On Fri, Dec 5, 2008 at 1:51 PM, Mark Roberts
<mailing_lists@pandapocket.com> wrote:
>
> On Thu, 2008-12-04 at 13:01 -0500, Sean Davis wrote:
>> I am happy to see NaN and infinity handled in input.  I would now like
>> to compute aggregates (avg, min, max, etc) on columns with NaN values
>> in them.  The standard behavior (it appears) is to have the aggregate
>> return NaN if the data contain one-or-more NaN values.  I am used to
>> using coalesce with NULL values, but that doesn't work with NaN.  I
>> can deal with these using CASE statuement to assign a value, but is
>> there a standard way of dealing with the NaN (or Infinity, for that
>> matter) cases to get a behvavior where they are "ignored" by an
>> aggregate?
>>
>> Thanks,
>> Sean
>>
>
> Have you considered using a where clause?

Thanks, Mark.  Yes.  I have about 20 columns over which I want to
simultaneously compute aggregates.  Each has NaN's in different rows,
so a where clause won't do what I need.

The CASE statement approach works fine, though.

Sean