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