At 07:38 PM 16/04/2009, you wrote:

>I've got a problem on an existing production system that I've just
>written some new reports for.
>
>The database has a double precision field that I need to round to 2
>dp. If I simply sum the values and cast then I get the correct
>rounding, except that the query I'm running needs a group by. When
>the group by is added the resulting value changes.
>
>Have I got something fundamentally wrong with my thinking?
>
>
>Thanks
>
>Ian
>
>isql localhost:/home/ian/test.gdb -z
>ISQL Version: LI-V1.5.4.4910 Firebird 1.5
>Server: LI-V1.5.4.4910 Firebird 1.5,LI-V1.5.4.4910 Firebird 1.5/tcp (localhost)/P102LI-V1.5.4.4910 Firebird 1.5/tcp (ian-desktop2)/P10
>Database: localhost:/home/ian/test.gdb
>SQL> create table tmp (id integer, parent_id integer, qty double precision);
>SQL> insert into tmp values (1,1,86.85);
>SQL> insert into tmp values (2,1,-14.475);
>SQL> insert into tmp values (3,1,57.90);
>SQL> select sum(qty) from tmp;
>
> SUM
>=======================
>
> 130.2750000000000
>
>SQL> select cast(sum(qty) as decimal(9,2)) from tmp;
>
> CAST
>============
>
> 130.28
>
>SQL> select parent_id, cast(sum(qty) as decimal(9,2)) from tmp group by parent_id;
>
> PARENT_ID CAST
>============ ============
>
> 1 130.27

Double precision numbers are ~ 64-bit. I suspect that aggregating them as a cast to a 32-bit number is causing the group members' floats to be truncated to single precision, rather than performing the sum on the original doubles. Cast to DECIMAL(18,2) to see whether that makes a difference. If the result is still unsatisfactory, you'd need to ask the accountant what rounding algorithm s/he wants and design an expression accordingly.

From a design point of view, it will also be important to decide whether a double is what you actually want for your Quantity data. It will obviously matter if it's weighing gold dust...but not if you're selling cables or fabric by the metre...and definitely wrong if your quantities are unitary, e.g. a dozen red roses or 100 goldfish. ;-)

./heLen