Subject Re: Aggregate Functions (re: number of unions in Firebird Seslect) Adam 2005-11-07T22:41:33Z
> 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).

Helen is right, I actually didn't read your question properly. The SQL
Standard says that integer divisions are truncated. I suppose if they
rounded then we would all get into arguments about where 0.5 should be
rounded, either up or to the closest even number.

> 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 :-]

I vote we blame the French too ;)

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

Average is not an integer division. It is an internal function that
accepts a number of inputs (probably floats), sums them, and then
divides by the number, leaving another float.

You could of course case the result of avg back to a smallint, but
that would truncate. Most programming languages work the reverse way.
They preserve the result by changing the result type to float rather
than some crude rounding.

But it is two different issues. It reacts according to specification,
but it may not be immediately obvious.