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.