--- In firebird-support@yahoogroups.com, "Tony Masefield"
<polsolsa@y...> wrote:

>
> Hi All,
>
> Following on from the initial thread:
>
> Select Cast(AVG(Col_1) as Float) returns:
> 5.985... if Col_1 is a Float
> 5 if Col_1 is a SmallInt (or Integer),
> 5.9 if Col_1 is Decimal(4,1)
> 5.98 if Col_1 is Decimal(4,2)
> 5.985 if Col_1 is Decimal(4,3) etc
>
> Therefore it would seem that Firebird (1.5.2) computes the Average
> based on the underlying field type and truncates (rather than
> rounds) the result to match it.
> This is a 'caveat' for those using the FB aggregate AVG function. I
> would have hoped that FB would have returned 6, rather than 5, for
> the initial Col_1 field type of SmallInt (as I am not interested in
> the first decimal place - which is why I declared the field as
> SmallInt in the first place).
> Has this been considered for FB 2.0?

Interesting, totally annoying, a gotcha even, but I bet it is how the
standard says it should be done.

Try something like.

Select Cast(AVG(Cast(Col_1 as Float)) as Float)

In fact, if you cast Col_1 as Float, you probably don't need to cast
the average, so this should work too.

Select AVG(Cast(Col_1 as Float))

Adam