Hi all,
I am currently testing on the average results for int and smallint, using
h2-1.3.175 (also simulated on h2-1.4.182). However, the results are not
rounded accurately.
Test case :
create table testAvgDistinct ( colInt int, colSINT smallint);
insert into testAvgDistinct (colInt, colSINT) values (NULL,NULL);
insert into testAvgDistinct (colInt, colSINT) values (-1,-1);
insert into testAvgDistinct (colInt, colSINT) values (0,0);
insert into testAvgDistinct (colInt, colSINT) values (1,1);
insert into testAvgDistinct (colInt, colSINT) values (10,10);
select sum(colInt) as sumINT, count(colInt) as countINT, avg(colInt)
as avgINT, sum(colSInt) as sumSINT, count(colSInt) as countSINT,
avg(colSInt) as avgSINT from testAvgDistinct;
For both int and smallint, sum = 10, count = 4, avg = 2 . Mathematically,
avg is 2.5 and should be rounded to 3. However, the result given is 2.
The same goes for avg(distinct).
Thanks!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
package org.H2.samples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
public class TestAvgDistinct {
public static void main(String[] args) throws Exception {
// delete the database named 'test' in the user home directory
DeleteDbFiles.execute("~", "test", true);
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/test;MV_STORE=FALSE");
Statement stat = conn.createStatement();
// this line would initialize the database
// from the SQL script file 'init.sql'
// stat.execute("runscript from 'init.sql'");
stat.execute("create table testAvgDistinct ( colInt int, colSInt smallint)");
stat.execute("insert into testAvgDistinct (colInt, colSInt) values (NULL,NULL)");
stat.execute("insert into testAvgDistinct (colInt, colSInt) values (-1,-1)");
stat.execute("insert into testAvgDistinct (colInt, colSInt) values (0,0)");
stat.execute("insert into testAvgDistinct (colInt, colSInt) values (1,1)");
stat.execute("insert into testAvgDistinct (colInt, colSInt) values (10,10)");
ResultSet rs = stat
.executeQuery("select sum(colInt) as sumINT, count(colInt) as countINT, avg(colInt) as avgINT, sum(colSInt) as sumSINT, count(colSInt) as countSINT, avg(colSInt) as avgSINT from testAvgDistinct");
while (rs.next()) {
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
System.out.println("Column Name :"
+ rs.getMetaData().getColumnName(i));
System.out.println("Column Value :" + rs.getString(i));
}
}
stat.close();
conn.close();
}
}