Ask Your Question
0

Base Average Function Bug?

asked 2020-08-27 11:30:45 +0100

JohnnyG gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
3

answered 2020-08-27 13:14:17 +0100

cpb gravatar image

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.

edit flag offensive delete link more

Comments

Ah OK thanks, another LO quirk I guess

JohnnyG gravatar imageJohnnyG ( 2020-08-28 10:10:50 +0100 )edit
1

answered 2020-08-27 11:50:35 +0100

Opaque gravatar image

updated 2020-08-27 11:51:04 +0100

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-27 11:30:45 +0100

Seen: 73 times

Last updated: Aug 27 '20