D2009 DBISAM latest

I have a record with a date and 4 vales. I want to sum these 4 values for a
particular record but only if the individual value is greater than zero
(ignoring zero values). Sort of like below in pseudo-code:

average(If Value1 > 0 + If Value2 > 0 + If Value3 > 0 + If Value4 > 0)
where date = SomeDate

TIA, Sean

I assume you have negatives in there (otherwise adding a 0 value does not
change sum in any way).

There is no average i think in DBISAM (there is an aggragate AVG function
but that's not what you need).

So sampel sql (not tested) could be something like :

select ((  IF( v1<0 THEN 0 ELSE V1) +  IF( v2<0 THEN 0 ELSE V2) + IF( v3<0
THEN 0 ELSE V3) + IF( v4<0 THEN 0 ELSE V4)) / 4) as AverageValue from
mytable

Update column and table names

Raul

"Sean McDermott" <Sean@HorizonCanada.com> wrote in message
news:3A5775F0-2AD1-452E-9E8D-649C886FCFBB@news.elevatesoft.com...
> D2009 DBISAM latest
>
> I have a record with a date and 4 vales. I want to sum these 4 values for
> a particular record but only if the individual value is greater than zero
> (ignoring zero values). Sort of like below in pseudo-code:
>
> average(If Value1 > 0 + If Value2 > 0 + If Value3 > 0 + If Value4 > 0)
> where date = SomeDate
>
> TIA, Sean
>

Sorry - did not get the point of ignoring 0 values to compute average.

So revised (uglier) sql would be somethign like this:

select COALESCE( (IF( v1>0 THEN V1 ELSE 0) +  IF( v2>0 THEN V2 ELSE 0) +
IF( v3>0 THEN V3 ELSE 0) + IF( v4>0 THEN V4 ELSE 0)) /
(sign(V1)+sign(V2)+sign(V3)+sign(V4)),0) as Aver from testavg

This assumes no negative values and "cheats" by using SIGN function - if you
need to ignore negatives then IF instead of SIGN would be better but longer
sql (e.g. sign(v1) should be IF(v1>0 THEN 1 ELSE 0) ). Otherwise sign
results in -1 for negative and thus the denominator calculation might be
off.

I'm sure somebody else can suggest a cleaner way of doing this.

Raul

"Raul" <raul@raul.ca> wrote in message
news:3FCEB959-A16B-44AA-86F0-B725C022BF8B@news.elevatesoft.com...
>I assume you have negatives in there (otherwise adding a 0 value does not
>change sum in any way).
>
> There is no average i think in DBISAM (there is an aggragate AVG function
> but that's not what you need).
>
> So sampel sql (not tested) could be something like :
>
> select ((  IF( v1<0 THEN 0 ELSE V1) +  IF( v2<0 THEN 0 ELSE V2) + IF( v3<0
> THEN 0 ELSE V3) + IF( v4<0 THEN 0 ELSE V4)) / 4) as AverageValue from
> mytable
>
> Update column and table names
>
> Raul
>
> "Sean McDermott" <Sean@HorizonCanada.com> wrote in message
> news:3A5775F0-2AD1-452E-9E8D-649C886FCFBB@news.elevatesoft.com...
>> D2009 DBISAM latest
>>
>> I have a record with a date and 4 vales. I want to sum these 4 values for
>> a particular record but only if the individual value is greater than zero
>> (ignoring zero values). Sort of like below in pseudo-code:
>>
>> average(If Value1 > 0 + If Value2 > 0 + If Value3 > 0 + If Value4 > 0)
>> where date = SomeDate
>>
>> TIA, Sean
>>
>
>

Hi Raul, thanks. I used the AVG function with an embedded IF that uses the
value if positive otherwise ignores it. Works well. Thanks for your help,
Sean

"Raul" <raul@raul.ca> wrote in message
news:B9BC28E7-DDD3-421C-AE64-37948DD55ECE@news.elevatesoft.com...
> Sorry - did not get the point of ignoring 0 values to compute average.
>
> So revised (uglier) sql would be somethign like this:
>
> select COALESCE( (IF( v1>0 THEN V1 ELSE 0) +  IF( v2>0 THEN V2 ELSE 0) +
> IF( v3>0 THEN V3 ELSE 0) + IF( v4>0 THEN V4 ELSE 0)) /
> (sign(V1)+sign(V2)+sign(V3)+sign(V4)),0) as Aver from testavg
>
> This assumes no negative values and "cheats" by using SIGN function - if
> you need to ignore negatives then IF instead of SIGN would be better but
> longer sql (e.g. sign(v1) should be IF(v1>0 THEN 1 ELSE 0) ). Otherwise
> sign results in -1 for negative and thus the denominator calculation might
> be off.
>
> I'm sure somebody else can suggest a cleaner way of doing this.
>
> Raul
>
> "Raul" <raul@raul.ca> wrote in message
> news:3FCEB959-A16B-44AA-86F0-B725C022BF8B@news.elevatesoft.com...
>>I assume you have negatives in there (otherwise adding a 0 value does not
>>change sum in any way).
>>
>> There is no average i think in DBISAM (there is an aggragate AVG function
>> but that's not what you need).
>>
>> So sampel sql (not tested) could be something like :
>>
>> select ((  IF( v1<0 THEN 0 ELSE V1) +  IF( v2<0 THEN 0 ELSE V2) + IF(
>> v3<0 THEN 0 ELSE V3) + IF( v4<0 THEN 0 ELSE V4)) / 4) as AverageValue
>> from mytable
>>
>> Update column and table names
>>
>> Raul
>>
>> "Sean McDermott" <Sean@HorizonCanada.com> wrote in message
>> news:3A5775F0-2AD1-452E-9E8D-649C886FCFBB@news.elevatesoft.com...
>>> D2009 DBISAM latest
>>>
>>> I have a record with a date and 4 vales. I want to sum these 4 values
>>> for a particular record but only if the individual value is greater than
>>> zero (ignoring zero values). Sort of like below in pseudo-code:
>>>
>>> average(If Value1 > 0 + If Value2 > 0 + If Value3 > 0 + If Value4 > 0)
>>> where date = SomeDate
>>>
>>> TIA, Sean
>>>
>>
>>
>
>