Subject Re: Aggregate Functions (re: number of unions in Firebird Seslect)
Author Tony Masefield
--- In, Helen Borrie <helebor@t...>
> >Interesting, totally annoying, a gotcha even, but I bet it is how
> >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
> 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
> result. It's not rounded at all. Any decimal part just gets
> 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.
> 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!