[sqlite] testing Avg() function in other database engines

[Dennis Cote]

Hi All,

I have a question that I hope you can help with. I would like to know 
what results other database engines (i.e. mySQL, PostgreSQL, Firebird, 
Oracle, MS SQL, etc) give for the following SQL.

   create table t (a integer, b real);
   insert into t values (3, 3);
   insert into t values (3, 3);
   insert into t values (4, 4);
   select avg(a), avg(b) from t;

In SQLite I get the following:

   SQLite version 3.3.2
   Enter ".help" for instructions
   sqlite> create table t (a integer, b real);
   sqlite> insert into t values (3, 3);
   sqlite> insert into t values (3, 3);
   sqlite> insert into t values (4, 4);
   sqlite> select avg(a), avg(b) from t;
   3.33333333333333|3.33333333333333

I'm particularly interested in the value of avg(a). My reading of the 
standard leads me to believe that avg(a) should be 3 (i.e. an integer 
value) and not a floating point value. This is similar to the earlier 
discussion of the results for division with integers (i.e. 5/2 -> 2 vs 
5/2 -> 2.5).

I would like to know if other databases follow the standard here or not. 
If you have ready access to another database, please try this and post 
the result. It will save me lots of time setting up other programs (and 
hopefully get info on programs I don't have access to like Oracle).

TIA
Dennis Cote