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.

Reply via email to