Search code examples
sqlsumrounding-errordivide

sum divided values problem (dealing with rounding error)

I've a product that costs 4€ and i need to divide this money for 3 departments. On the second column, i need to get the number of rows for this product and divide for the number of departments.

My query:

select
   department, totalvalue,
   (totalvalue / (select count(*) from departments d2 where d2.department = p.product))
       dividedvalue
from products p, departments d
where d.department = p.department

Department  Total Value  Divided Value
----------  -----------  -------------
A           4            1.3333333
B           4            1.3333333
C           4            1.3333333

But when I sum the values, I get 3,999999. Of course with hundreds of rows i get big differences... Is there any chance to define 2 decimal numbers and round last value? (my results would be 1.33 1.33 1.34) I mean, some way to adjust the last row?

Solution

  • With six decimals of precision, you would need about 5,000 transactions to notice a difference of one cent, if you round the final number to two decimals. Increasing the number of decimals to an acceptable level would eliminate most issues, i.e. using 9 decimals you would need about 5,000,000 transactions to notice a difference of a cent.