sum divided values problem (dealing with rounding

问题:

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?

回答1:

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.

回答2:

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.

回答3:

Maybe you can make a forth row that will be Total - sum(A,B,C). But it depends on what you want to do, if you need exact value, you can keep fractions, else, truncate and don't care about the virtual loss

回答4:

Also can be done simply by adding the rounding difference of a particular value to the next number to be rounded (before rounding). This way the pile remains always the same size.