At 10:21 PM 6/11/2005 +0000, you wrote:

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

Why? when he wants a smallint result? If you average floats, you'll get a
float result. He'll have to invoke a ROUND() UDF to get that result
rounded to int (or use the old banker's hack!! see below)

If the field being averaged is a smallint then the returned result will be
smallint, too. Integer/integer division in SQL produces an integer
result. It's not rounded at all. Any decimal part just gets lopped
off. So Tony got the right result, even though he hoped he wouldn't. :-)

The banker's hack:

select cast(avg(cast(Col_1 as float)+ 0.5) as smallint) as av from ...


The moral of this tale is that, in SQL, surprise! surprise! you have to
think about your output requirements when choosing a number type. There
are actually *reasons* why DBMSs support a range of number types....and the
choice is always a trade-off between precision and accuracy.

./heLen