Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
Averages |
Thu, Oct 29 2009 11:02 AM | Permanent Link |
"Sean McDermott" | 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 |
Thu, Oct 29 2009 2:09 PM | Permanent Link |
"Raul" | 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 > |
Thu, Oct 29 2009 2:41 PM | Permanent Link |
"Raul" | 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 >> > > |
Thu, Oct 29 2009 2:47 PM | Permanent Link |
"Sean McDermott" | 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 >>> >> >> > > |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |