Hi, I see MySQL and PostgreSQL always return a decimal.
However, H2 and HSQLDB and Apache Derby all return an integer. Regards, Thomas On Tue, Nov 4, 2014 at 10:15 AM, Amanda Yap <[email protected]> wrote: > Additional information, if the table only contains the following data > > insert into testAvgDistinct (colInt, colSInt) values (2,2); > insert into testAvgDistinct (colInt, colSInt) values (1,1); > > The result gives sum = 3, count = 2, avg = 1. This is caused by the > rounding issue. It works on Big integer though. > > Thanks! > > > > > On Tuesday, November 4, 2014 4:17:46 PM UTC+8, Amanda Yap wrote: >> >> 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. > -- 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.
