--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

>
> >Interesting, totally annoying, a gotcha even, but I bet it is how

the

> >standard says it should be done.
> >
> 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
>

Hmmmm :-$ (The 'emoticon' is me keeping my mouth zipped)
Didn't succeed.
I would have thought that the object, even of an integer division,
would have been to return a result as accurate as possible which
IMHO means that the results should match the closest integer
(smallint) which is not the case (as .999' is truncated).
Tested my Casio desktop calculator which has a setting for zero
decimals and, lo and behold, it also truncates the result. Must tell
Casio their calculators are deffective ..
I suppose somewhere there is an international standard that decided
this outcome somewhere. Would like to know who ...
Possibly it's a hang-over from the French Revolution (Madame
Guillotine) or dictated by the banks for calculating credit
interest :-]
One way or the other, if FB returns a smallint as the result of
smallint divisions (i.e. average), it still fails to explain why FB
had a fit when it tried to return the result of AVG in columns that
were already 'delineated' as being smallint (Min and Max).
Just rambling :-)

Thanks, as ever, for the help!