sum divided values problem (dealing with rounding error)

Question

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?

Answer 1

In order to handle this, for each row you would have to do the following:

• Perform the division
• Round the result to the appropriate number of cents
• Sum the difference between the rounded amount and the result of the division operation
• When the sum of the differences exceeds the lowest decimal place (in this case, 0.01), add that amount to the results of the next division operation (after rounding).

This will distribute fractional amounts evenly across the rows. Unfortunately, there is no easy way to do this in SQL with simple queries; it's probably better to perform this in procedural code.

As for how important it is, when it comes to financial applications and institutions, things like this are very important, even if it's only by a penny, and even if it can only happen every X number of records; typically, the users want to see values tie to the penny (or whatever your unit of currency is) exactly.

Most importantly, you don't want to allow for an exploit like "Superman III" or "Office Space" to occur.