"Jackson, DeJuan" <djackson@cpsgroup.com> writes:
> What does the spec have to say? It bothers me somewhat that an AVG is
> expected to return an integer result at all. Isn't the Average of 1
> and 2, 1.5 not 1?
That bothered me too. The draft spec that I have sez:
b) If SUM is specified and DT is exact numeric with scale S, then the data type of the result is exact numeric with
implementation-defined precision and scale S.
c) If AVG is specified and DT is exact numeric, then the data type of the result is exact numeric with
implementation- defined precision not less than the precision of DT and implementation-defined scale not less than
thescale of DT.
d) If DT is approximate numeric, then the data type of the result is approximate numeric with
implementation-defined precision not less than the precision of DT.
65)Subclause 6.5, "<set function specification>": The precision of the value derived from application of the SUM
functionto a data type of exact numeric is implementation-defined.
66)Subclause 6.5, "<set function specification>": The precision and scale of the value derived from application of
theAVG function to a data type of exact numeric is implementation-defined.
67)Subclause 6.5, "<set function specification>": The preci- sion of the value derived from application of the SUM
func- tion or AVG function to a data type of approximate numeric is implementation-defined.
This would seem to give license for the result of AVG() on an int4 field
to be NUMERIC with a fraction part, but not FLOAT. But I suspect we
could get away with making it be FLOAT anyway. Anyone know what other
databases do?
regards, tom lane