In Summary, we have 4 database engines that appear to follow the
standard, and 6 that do not.
Standard:
MS SQL 2000
Firebird 1.5
MS SQL 2005
DB2 8.2
Non-standard:
MS Access
PostgreSQL 7.3
PostgreSQL 8.1.2
MySQL 5.0
Informix 7.31
Oracle 10.1
It is also interesting to note that MySQL seems to return different
precision for the two averages for some reason.
I would suggest that SQLite should be modified to comply with the
standard and return an integer value for the average of a column
declared as integer. This will eliminate the inconsistency that Richard
note between avg() and sum()/count(). This would not change the behavior
for columns declared are real, or columns that are untyped.
Do any of you have, or know of, an application that would be adversely
affected if this change is made to SQLite?
Dennis Cote