# Sum of Round vs Round of Sum

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.

• The column names imply that you need "Banking rules". Too bad. You will have to code the rules yourself. Nov 20 at 16:37

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.

• Thank you for the comprehensive answer and explanation. Nov 20 at 10:03
• You're welcome! But there's a problem: if the developers of the app used floats, it means they don't know about this problem, which means they didn't take steps to avoid it, so you can never trust what comes out of this app... quite problematic for accouning... Nov 20 at 10:05
• Yeah, the app is pretty old, started on 2002 so I expect these kind of errors. I'll still open an issue in their Github to suggest changing to cents and store integers. Nov 20 at 10:10
• Since the number of decimals shown can be changed in the settings, how would you go about storing a value that involves more than 2 decimals? I work in a retail electric company so we have to deal with €/kWh prices with multiple decimals, I was thinking that, when printing, we could divide the integer by 10^number of decimals shown, but that would mean that the integers stored have to change every time the number of decimals show changes, so how would that work? Always asume we're storing the max number of decimals allowed regardless of the user settings? Nov 20 at 10:43
• There is a Decimal class for PHP github.com/piggly-dev/php-decimal Nov 20 at 11:05