| Subject | Re: casting to decimal(9,2) gives different answers if used with a group by clause |
|---|---|
| Author | wibbleian2 |
| Post date | 2009-04-17T10:50:10Z |
--- In firebird-support@yahoogroups.com, Kjell Rilbe <kjell.rilbe@...> wrote:
select cast(sum(nett) as decimal(9,2)) from invcline;
And elsewhere I have code that does:
select parent_id, cast(sum(nett) as decimal(9,2)) from invcline group by parent_id;
The results from these 2 queries are different for the same parent_id. That surprised me.
Working back from where I have the problem I now have a permanent solution for this specific case that only applies to me.
I also have stashed in my head that "group by" appears to do some internal rounding that I wasn't expecting so I wont be surprised if this happens again. I wont like it if it does, but I wont be surprised. This is the general solution to my question. If you can call it a solution.
Obviously the other solution is to persuade customers that the result of multiplying lots of precise numbers together and then rounding arbitrarily in different places will give you rounding errors that can be comfortably ignored. But I've not met a user that can be persuaded of anything, so I'll go back to waiting for the revolution.
Ian
p.s. chatting to our company accountant about rounding he pointed us at our BT bill which he correctly predicted would have a penny rounding error on it. He also showed me a big DIY chaing invoice he had that demonstrated them screwing you for a couple of pence across all the lines and then diddling the vat man out of the vat on it. Apparently if you are big enough you can just ignore some things!
>The precision of the calculation is not the problem. The problem is that I have one piece of code that for a given parent does:
> OK, so how many decimal places do the added terms require? 4? Cast to
> that precision, then:
>
select cast(sum(nett) as decimal(9,2)) from invcline;
And elsewhere I have code that does:
select parent_id, cast(sum(nett) as decimal(9,2)) from invcline group by parent_id;
The results from these 2 queries are different for the same parent_id. That surprised me.
Working back from where I have the problem I now have a permanent solution for this specific case that only applies to me.
I also have stashed in my head that "group by" appears to do some internal rounding that I wasn't expecting so I wont be surprised if this happens again. I wont like it if it does, but I wont be surprised. This is the general solution to my question. If you can call it a solution.
Obviously the other solution is to persuade customers that the result of multiplying lots of precise numbers together and then rounding arbitrarily in different places will give you rounding errors that can be comfortably ignored. But I've not met a user that can be persuaded of anything, so I'll go back to waiting for the revolution.
Ian
p.s. chatting to our company accountant about rounding he pointed us at our BT bill which he correctly predicted would have a penny rounding error on it. He also showed me a big DIY chaing invoice he had that demonstrated them screwing you for a couple of pence across all the lines and then diddling the vat man out of the vat on it. Apparently if you are big enough you can just ignore some things!