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?

What database is this? Oracle? MS SQL Server? MySQL? Also, what datatype is `totalvalue`? — FrustratedWithFormsDesigner, Jan 24 '11 at 17:18
can't you just use the total from column 2? why do you need to re-sum the values with rounding error? — Randy, Jan 24 '11 at 17:18
Can you post the DDL of your table "department" so we can see the data types and such? — Mike Sherrill 'Cat Recall', Jan 24 '11 at 17:19
MS SQL Server. totalvalue is decimal(11,2). I can't use totalvalue because after that I need to calculate the value of each department. The real problem it's a bit complicated. Each product can have variable number of departments. — Fernando, Jan 24 '11 at 17:54
A similar problem with php solution can be found here: http://stackoverflow.com/questions/11612045/correct-sums-with-dividing-sums-countering-rounding-errors — Per Enström, Jul 23 '12 at 14:32

score 1 · Answer 1 · answered Jan 24 '11 at 18:34

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.

score 0 · Answer 2 · answered Jan 29 '15 at 08:28

0

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.

answered Jan 29 '15 at 08:28

Martin

1,385
15
21

score 0 · Answer 3 · answered Jan 24 '11 at 17:19

0

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

answered Jan 24 '11 at 17:19

Karudosu

89
2
11

Benjamin · Accepted Answer · 2011-01-24T17:33:19.893

0

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.

edited Jan 24 '11 at 17:33

answered Jan 24 '11 at 17:23

Benjamin

11,560
13
70
119

When doing reporting on very large databases, 5 000 records is no problem at all. 5 000 000 is also easy and in many cases, *any* discrepancy will be noticed and questioned. – FrustratedWithFormsDesigner Jan 24 '11 at 17:25
That's great! So this is a non-problem. My query has around 10milion lines so and I have enough precision. I was thinking about some funcion to solve this problem, but I think I won't do it. Thank you! – Fernando Jan 24 '11 at 17:35
the real solution is to represent currency values in the smallest increment available. So instead of 4 euros, store 400 eurocents. Then you will have full precision. – Spike Gronim Jan 24 '11 at 18:12
I had thought about that solution, but 400 cents / 3 still equals 133.33333333... which does not solve the issue. It works for direct totals on his 2nd column, but not for totals of the divided amount in col 3 – Benjamin Jan 24 '11 at 18:14

rtev · Answer 5 · 2019-08-08T16:26:09.200

Here's a TSQL (Microsoft SQL Server) implementation of the algorithm provided by Martin:

-- Set parameters.
DECLARE @departments INTEGER = 3;
DECLARE @totalvalue DECIMAL(19, 7) = 4.0;

WITH
CTE1 AS
(
    -- Create the data upon which to perform the calculation.
    SELECT

        1 AS Department
        , @totalvalue AS [Total Value]
        , CAST(@totalvalue / @departments AS DECIMAL(19, 7)) AS [Divided Value]
        , CAST(ROUND(@totalvalue / @departments, 2) AS DECIMAL(19, 7)) AS [Rounded Value]

    UNION ALL

    SELECT

        CTE1.Department + 1
        , CTE1.[Total Value]
        , CTE1.[Divided Value]
        , CTE1.[Rounded Value]

    FROM

        CTE1

    WHERE

        Department < @departments
),

CTE2 AS
(
    -- Perform the calculation for each row.
    SELECT

        Department
        , [Total Value]
        , [Divided Value]
        , [Rounded Value]
        , CAST([Divided Value] - [Rounded Value] AS DECIMAL(19, 7)) AS [Rounding Difference]
        , [Rounded Value] AS [Calculated Value]

    FROM

        CTE1

    WHERE

        Department = 1

    UNION ALL

    SELECT

        CTE1.Department
        , CTE1.[Total Value]
        , CTE1.[Divided Value]
        , CTE1.[Rounded Value]
        , CAST(CTE1.[Divided Value] + CTE2.[Rounding Difference] - ROUND(CTE1.[Divided Value] + CTE2.[Rounding Difference], 2) AS DECIMAL(19, 7))
        , CAST(ROUND(CTE1.[Divided Value] + CTE2.[Rounding Difference], 2) AS DECIMAL(19, 7))

    FROM

        CTE2

            INNER JOIN CTE1
                ON CTE1.Department = CTE2.Department + 1

)

-- Display the results with totals.
SELECT 

    Department
    , [Total Value]
    , [Divided Value]
    , [Rounded Value]
    , [Rounding Difference]
    , [Calculated Value]

FROM 

    CTE2

UNION ALL

SELECT

    NULL
    , NULL
    , SUM([Divided Value])
    , SUM([Rounded Value])
    , NULL
    , SUM([Calculated Value])

FROM

    CTE2

;

Output:

You can plug in whatever numbers you want at the top. I'm not sure if there is a mathematical proof for this algorithm.

sum divided values problem (dealing with rounding error)

5 Answers5

Linked