The dogdy thing about it, is that you seem to treat the symptoms, not the cause.

If you tried

select cast(cast(130.2749 as decimal(18,3)) as decimal(18,2))
from rdb$database

you would see that this rounded to 130.28, whereas the straight

select cast(130.2749 as decimal(18,2))
from rdb$database

would get the more 'correct' 130.27.

Your problem is the imprecision of double precision. Never treat a double precision value as if it was precise, 130.275 is only approximately 130.275 and if rounding to two decimals it may go either way (though I was surprised by your findings that it varies with GROUP BY). Your solution is fine if you want values around 130.2745 - 130.2749 to round upwards, if those should round downwards you have to add more precision to the inner cast. At one point, you have to stop and say that something e.g. approximately 130.27499999 is OK to round to 130.28.

HTH,
Set

> Casting to 18,2 made no difference. Applying a dodgy hack the following works "fine":
>
> select parent_id, cast(cast(sum(qty) as decimal(18,3)) as decimal(18,2)) from tmp group by parent_id;
>
> At least it gives the correct answer in this scenario...