"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 the scale 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 function to 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 the AVG 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