A master/detail situation and have a sum of detail stored in a field in master table. Sum of detail is computed as multiple of two fieleds; one have three decimals one only two; both "float
type". When I sum those fields in master table I do not get the same value as if I sum it in detail.
please advise

Depends no whether you are off by small fractional numbers or something
more significant. Small numbers could be the result of lost precision
due to the fact that floating point numbers are stored as binary
fractions and so many decimal fractions are stored only as an
approximation. The effects of these small errors can be magnified by
multiplication and summing, but unless you have a huge quantity of
records I wouldn't expect that it would affect the significant digits in
your result.

When displaying a floating point value it is good practice to always
display and store a rounded amount to reflect the precision of the
calculations. In your case you should be rounding to 5 decimal places
before storing the numbers or displaying the totals.

If you are off by something more significant you may have some orphaned
detail records. It is also possible that some detail records are have
more decimal digits than you expect. Rounding the values before the
calculations would take care of the discrepancy in sums, but would not
address the problems with the validity of the data.

Sean






Andrej Bivic wrote:
> A master/detail situation and have a sum of detail stored in a field in master table. Sum of detail is computed as multiple of two fieleds; one have three decimals one only two; both "float
> type". When I sum those fields in master table I do not get the same value as if I sum it in detail.
> please advise
>

I have 5000 records in master table per day and 20000 records in detail table per day. And yes, some records do have more decimals as 5. But I tried all sort of thing and among then
approximation befor storing but no matter waht I get an error on second decimal after summing. Tried to approximate on different levels (2, 3,4 and 5 decimals) althow I need results in 2
decimal numbers.


Sean McCall <someone@somewhere.net> wrote:

Depends no whether you are off by small fractional numbers or something
more significant. Small numbers could be the result of lost precision
due to the fact that floating point numbers are stored as binary
fractions and so many decimal fractions are stored only as an
approximation. The effects of these small errors can be magnified by
multiplication and summing, but unless you have a huge quantity of
records I wouldn't expect that it would affect the significant digits in
your result.

When displaying a floating point value it is good practice to always
display and store a rounded amount to reflect the precision of the
calculations. In your case you should be rounding to 5 decimal places
before storing the numbers or displaying the totals.

If you are off by something more significant you may have some orphaned
detail records. It is also possible that some detail records are have
more decimal digits than you expect. Rounding the values before the
calculations would take care of the discrepancy in sums, but would not
address the problems with the validity of the data.

Sean






Andrej Bivic wrote:
> A master/detail situation and have a sum of detail stored in a field in master table. Sum of detail is computed as multiple of two fieleds; one have three decimals one only two; both "float
> type". When I sum those fields in master table I do not get the same value as if I sum it in detail.
> please advise
>

Hi Andrej,
You didn't say how much you are off. As Sean suggested, if you are off
significantly, you could have oftened detailed records and you may be
wasting your time checking the rounding and precision. Have you verified
that each and every detailed record has a corresponding master record ?

regards
Gregory Sebastian

Let sa, the sum is around 23000 and I am off 0.01
And yes, every dtail has a corresponding master and botha tabels seems to be ok.

"Gregory Sebastian" <gregorys@ezysoft-dev.com> wrote:

Hi Andrej,
You didn't say how much you are off. As Sean suggested, if you are off
significantly, you could have oftened detailed records and you may be
wasting your time checking the rounding and precision. Have you verified
that each and every detailed record has a corresponding master record ?

regards
Gregory Sebastian

"Andrej" <andrej.bivic@abitrade,si> wrote in message
news:E717D642-64F2-4471-BAD0-3504551D65BF@news.elevatesoft.com...
> Let sa, the sum is around 23000 and I am off 0.01
> And yes, every dtail has a corresponding master and botha tabels seems to
> be ok.
>

It is doable, but you have to use the right data types or it becomes a
nightmare. And after each multiplication or division, you have to round to
two decimals.

Google round float and you'll find probably more than you need. Also, a
source of confusion is that Delphi currency is NOT DBISAM currency.

Robert

> Gregory Sebastian
>

Hi Andrej,
If the final precision required is only 2 decimals, then AFTER multiplying
but BEFORE summing, ensure that you round the result of each row to 2
decimals. You must do this when storing the detail totals in the master
record and when comparing the totals of all records from master and detail.
Both totals should be accurate to 2 decimals.

Regards
Gregory Sebastian

round(66.7846 , 2)  = 66.78
round(66.7850 , 2)  = 66.78   ??? is this ok??
round(66.7854 , 2)  = 66.79
round(66.7864 , 2)  = 66.79


"Gregory Sebastian" <gregorys@ezysoft-dev.com> wrote in message
news:875C280E-4A01-4431-A18C-CE541FECE8D0@news.elevatesoft.com...
> Hi Andrej,
> If the final precision required is only 2 decimals, then AFTER multiplying
> but BEFORE summing, ensure that you round the result of each row to 2
> decimals. You must do this when storing the detail totals in the master
> record and when comparing the totals of all records from master and
> detail. Both totals should be accurate to 2 decimals.
>
> Regards
> Gregory Sebastian

>>> round(66.7850 , 2)  = 66.78   ??? is this ok??
Yes, this is "Bankers" rounding. When the next digit is exactly mid-way
(meaning 5), bankers rounding always rounds up or down to the nearest even
number. Hence in bankers rounding :

RoundTo(66.7650 , 2)  = 66.76 (down)
RoundTo(66.7750 , 2)  = 66.78 (up)
RoundTo(66.7850 , 2)  = 66.78 (down)
RoundTo(66.7950 , 2)  = 66.80 (up)

If you want to always round up when the next digit is 5, in Delphi, you need
to use "SimpleRoundTo". SimpleRoundTo gives following results :

RoundTo(66.7650 , 2)  = 66.77 (up)
RoundTo(66.7750 , 2)  = 66.78 (up)
RoundTo(66.7850 , 2)  = 66.79 (up)
RoundTo(66.7950 , 2)  = 66.80 (up)

Regards
Gregory Sebastian

Once I round "in Delphi" and once I round "in SQL". But I need the same results. How do I
make sure, both oundings are the same?!
please, advice

"Gregory Sebastian" <gregorys@ezysoft-dev.com> wrote:

>>> round(66.7850 , 2)  = 66.78   ??? is this ok??
Yes, this is "Bankers" rounding. When the next digit is exactly mid-way
(meaning 5), bankers rounding always rounds up or down to the nearest even
number. Hence in bankers rounding :

RoundTo(66.7650 , 2)  = 66.76 (down)
RoundTo(66.7750 , 2)  = 66.78 (up)
RoundTo(66.7850 , 2)  = 66.78 (down)
RoundTo(66.7950 , 2)  = 66.80 (up)

If you want to always round up when the next digit is 5, in Delphi, you need
to use "SimpleRoundTo". SimpleRoundTo gives following results :

RoundTo(66.7650 , 2)  = 66.77 (up)
RoundTo(66.7750 , 2)  = 66.78 (up)
RoundTo(66.7850 , 2)  = 66.79 (up)
RoundTo(66.7950 , 2)  = 66.80 (up)

Regards
Gregory Sebastian