Sum of Round vs Round of Sum

Question

This is a very short sample of what I have in my database.

CREATE TEMPORARY TABLE temp_ledger(
    book_entry INT,
    credit DOUBLE(24, 8),
    debit DOUBLE(24, 8)
);

INSERT INTO
    temp_ledger(book_entry, credit, debit)
VALUES
    (1, 17.54500000, 0.00000000),
    (1, 0.00000000, 14.50000000),
    (1, 0.00000000, 3.04500000),
    (2, 0.00000000, 99.85500000),
    (2, 95.10000000, 0.00000000),
    (2, 4.75500000, 0.00000000);

SELECT
    book_entry,
    SUM(ROUND(debit, 2)) as sum_of_rounded_debit,
    SUM(ROUND(credit, 2)) as sum_of_rounded_credit,
    ROUND(SUM(debit), 2) as round_of_summed_debit,
    ROUND(SUM(credit), 2) as round_of_summed_credit,
    SUM(debit) as summed_debit,
    SUM(credit) as summed_credit
FROM
    temp_ledger
GROUP BY
    book_entry;

DROP TEMPORARY TABLE temp_ledger;

Output

temp_ledger

book_entry	sum_of_rounded_debit	sum_of_rounded_credit	round_of_summed_debit	round_of_summed_credit	summed_debit	summed_credit
1	17,54	17,55	17,55	17,55	17,54500000	17,54500000
2	99,86	99,86	99,86	99,85	99,85500000	99,85500000

I don't understand why sum_of_rounded_debit for book entry 1 is 17.54:

• 14.50 rounded should be 14.50
• 3.045 rounded should be 3.05

And round_of_summed_credit for book entry 2 is 99.85 if summed_credit is 99.855, why does rounding that give me 99.85 and not 99.86

I understand that sometimes decimals can be finicky but round_of_summed_credit in particular has me wondering "What?".

This came up trying to find errors in Dolibarr's llx_accounting_bookkeeping table, since sum of round give a certain rows as mismatched between credit and debit while round of sum give a different set of rows.

Using MariaDB 10.5.8

Answer 1

Floating point numbers work in base 2 so conversion to and from base 10 always introduces tiny errors. When they are converted to base 10 for display, this is (somewhat and usually but not always) hidden by rounding to less than the full number of digits... but if you expect floating point to be exact, it won't work.

For example, the number "3.045" cannot be encoded as floating point double precision, so the closest available representation is used:

3.04499999999999992895

When using round() with number of decimals 2 this rounds to:

3.04000000000000003553

This result is correct, because 3.044999... (which rounds to 3.04) is not 3.045 (which would round to 3.05). The problem is you believe that converting 3.045 to double precision floating point gives a result equal to 3.045. It does not.

Python demonstrates:

>>> round(3.045,2)
3.04

The non-exact nature of floats means they should never be used to represent exact base-10 numbers, especially in the context of accounting.

Fortunately this is a solved problems, as your database provides an exact fixed-point type: Numeric/Decimal.

You can specify the precision of these Decimal types as you wish. Usually in accounting the smallest unit is the cent, it is not possible to make a bank transfer or check with fractional cents, so the numbers are specified with a precision of 2 digits after decimal point. In other contexts like crypto where an unlucky owner of $DOGE sells you could have a transfer of 0.000002 BTC so you may wish to add more digits.

The whole point is that the value stored in the table is the exact amount of money that was transferred in the transaction, not an approximate conversion to float.

Once this is done, all calculations will be correct.

I have no idea what sum(round()) is supposed to achieve. If you want sum() of payments, then you should do the sum() of payments, not round them before summing.

There is a possible explanation: the designers of this database used floats for accounting, which means they have no idea what they're doing. So they used round() as an attempt to fix the errors introduced by using the wrong data type.

It won't work. You have to use the correct data type, or your results will always be wrong.

Note since the data in the table is in the wrong data type, conversion to Decimal may introduce issues. For example if the value "3.045" (actually 3.04499999999999992895) is stored, and you convert it to Decimal(8,2) you should check that it gives the result you want.

Since the whole app is written in php, and php has no Decimal type (only floats)... the usual way to do this in php is to convert everything into cents and use only integers. So calculations done in the app may also be wrong. Symptoms include sums calculated from php being different from sums calculated in the database.