The “Average” function in Base queries seems to have a bug! Say I have a table with a field call it “FieldA” set as an integer and the values are 1,3,4,2 if I run a query to find the average Base says 2. Ok fair enough the field type is Integer so the answer is truncated, but there seems to be no way of setting the query to give the correct answer ie 2.5 even if I set the query answer to be a “Number” type with 2 decimal places, I just get 2.00, Wrong. It is obviously very unlikely that the average of a set of integer values would give an integer answer. Interestingly! If I use the same query and set the function to “STDEV-POP” the answer is 1.12 which is correct with no need to set any decimal places. I’m running LO 6.4.6.2 x64 bit on Windows 10 and Firebird.
hello @JohnnyG,
you can return a decimal value like this:
select avg(1.00 * "MyFieldName") from...
precision is determined by the number of zeroes which follow the decimal point.
Ah OK thanks, another LO quirk I guess
Hello,
see https://firebirdsql.org/manual/nullguide-aggrfunc.html, which states (annotation in Table 7)
[a] If Field is of an integer type, AVG is always rounded towards 0.
Hope that helps.