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

[Jarosław Nozderko]

Sybase ASE 12.5.3:

3   3.33


Regards,
Jarek
 

> -Wiadomość oryginalna-
> Od: Dennis Cote [mailto:[EMAIL PROTECTED] 
> Wysłano: 8 lutego 2006 20:11
> Do: sqlite-users
> Temat: [sqlite] testing Avg() function in other database engines
> 
> 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.33|3.33
> 
> 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
> 

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

[Jim C. Nasby]

In the case of PostgreSQL, avg is actually returning numeric when handed
an int.

On Wed, Feb 08, 2006 at 11:26:37AM -0800, Rich Shepard wrote:
> On Wed, 8 Feb 2006, Paul Tomblin wrote:
> 
> >In PostgreSQL 7.3, I get the same:
> 
>   Also in postgres-8.1.2. I entered the reals with a decimal point; made no
> difference.
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |   Author of "Quantifying 
> Environmental
> Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy 
> Logic"
>  Voice: 503-667-4517 Fax: 
> 503-667-8863
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

[Marian Olteanu]

Probably the best solution would be to have the standard implementation 
activated by a PRAGMA command. This way, you don't steal functionality 
from people who want non-standard implementation and you also don't risk 
to break compatibility with existing software over SQLite (you have 
backward compatibity).



 On Wed, 8 Feb 2006, Dennis Cote wrote:

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

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

[Dennis Cote]

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

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

[Dennis Cote]

Oops...

That should, of course, be *inconsistency*.

On 2/8/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> [EMAIL PROTECTED] wrote:
>
> >
> >Did you try sum(a)/count(a) instead of avg(a).
> >
> >   SQLite version 3.3.3
> >   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), sum(a)/count(a) from t;
> >   3.33|3
> >
> >Kind of seems wrong, doesn't it
> >--
> >D. Richard Hipp   <[EMAIL PROTECTED]>
> >
> >
> >
> >
> Richard,
>
> This is a good point. This is exactly the kind of consistency that the
> standard developers try hard to eliminate. It's probably one of the
> reasons the standard says the average of an integer column should be an
> integer (or words to that effect).
>
> Dennis Cote
>

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

[Dennis Cote]

[EMAIL PROTECTED] wrote:



Did you try sum(a)/count(a) instead of avg(a).

  SQLite version 3.3.3
  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), sum(a)/count(a) from t;
  3.33|3

Kind of seems wrong, doesn't it
--
D. Richard Hipp   <[EMAIL PROTECTED]>


 


Richard,

This is a good point. This is exactly the kind of consistency that the 
standard developers try hard to eliminate. It's probably one of the 
reasons the standard says the average of an integer column should be an 
integer (or words to that effect).


Dennis Cote

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

[Thomas Briggs]

   Oracle 10.1

AVG(A) AVG(B)
-- --
3. 3. 

   SQL 2005

--- --
3   3.33

   DB2 8.2

1   2   
--- 
  3   +3.33E+000

> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 08, 2006 2:11 PM
> To: sqlite-users
> Subject: [sqlite] testing Avg() function in other database engines
> 
> 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.33|3.33
> 
> 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
> 

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

[Griggs, Donald]

FWIW, Informix 7.31 returns REALs for both averages and also for 
  sum(a)/count(a) 


Donald Griggs
Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 08, 2006 2:11 PM

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

[Nemanja Corlija]

On 2/8/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> 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;

MySQL 5.0
3.   3.3

Firebird 1.5
3   3.33

--
Nemanja Corlija <[EMAIL PROTECTED]>

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

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> 
> 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).
> 

Did you try sum(a)/count(a) instead of avg(a).

   SQLite version 3.3.3
   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), sum(a)/count(a) from t;
   3.33|3

Kind of seems wrong, doesn't it
--
D. Richard Hipp   <[EMAIL PROTECTED]>

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

[Rich Shepard]

On Wed, 8 Feb 2006, Paul Tomblin wrote:


In PostgreSQL 7.3, I get the same:


  Also in postgres-8.1.2. I entered the reals with a decimal point; made no
difference.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

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

[Jay Sprenkle]

On 2/8/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> 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.33|3.33

MS SQL 2000

3   3.3335

ms access

3.333.33

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

[Paul Tomblin]

Quoting Dennis Cote ([EMAIL PROTECTED]):
> 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.33|3.33

In PostgreSQL 7.3, I get the same:
cosdb=> create table t (a integer, b real);
CREATE TABLE
cosdb=> insert into t values (3, 3);
INSERT 973963 1
cosdb=>insert into t values (3, 3);
INSERT 973964 1
cosdb=>insert into t values (4, 4);
INSERT 973965 1
cosdb=>select avg(a), avg(b) from t;
avg |   avg
+--
 3. | 3.33
(1 row)

cosdb=>


-- 
Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/
Make backups before you try something new or interesting or experimental
or radical or if the day has a "y" in it.
  -- Chris Hacking

[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.33|3.33

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