Rounding as a result of multiplication

Question

I have this scenario: - multiply by 2 values which will be rounded to 2 boxes for the table to be inserted.

Follow the example:

CREATE TABLE #TMP (
    A DECIMAL(23,6),
    B DECIMAL(28,2)
)
INSERT INTO #TMP VALUES (0.002071, 84.50)
INSERT INTO #TMP VALUES (0.011500, 50.00)

SELECT *, 
    A * CAST(B AS FLOAT) res_cast,
    ROUND(A * CAST(B AS FLOAT),2) res_cast_round,
    A * B res,
    ROUND(A * B,2) res_round
FROM #TMP
DROP TABLE #TMP

The results in SQL Server 2012 are:

As we can see, doing cast in the second value for multiplication to the first account 0.002071 * 84.50 = 0.1749995, the result and rounding in this case is OK, resulting in 0.17. But in the second account 0.011500 * 50.00 = 0.575, rounding is wrong, resulting in 0.57.

So to solve the problem of the second account just simply take the cast in the second value: 0.011500 * 50.00 = 0.575000, rounding is OK, resulting in 0.58. But on the first count 0.002071 * 84.50 = 0.175000 comes out with the wrong result and when we give the value 0.18 is OK.

Any idea what it is?

Answer 1

Specific problem

How data is being stored as DECIMAL I understand that these are monetary values or other kind of numbers that require accuracy. So the right account is being made with the last two operations. That is, it is multiplying two exact decimal numbers and then doing the same rounding to 2 houses. It rounds up because this is the standard rounding behavior in SQL Server. It is possible to use other criteria. The pattern goes up in positive numbers from 5 on the box that should round. See the Wikipedia article to learn more about rounding (in Portuguese without details).

The rounding criterion needs to be part of the business rule. And after this rule is well defined, you have to adapt the code to serve it, based on the functioning of the language mechanism being used.

Everything seems to be all right. As one of them has 6 decimal places, and the other has 2 houses, the result was 3 houses, so there was an implicit rounding from 0.1749995 to 0.175. There the explicit rounding rounded to 0.18. That is, it seems all right.

I got a gambit:

SELECT *, 
    A * CAST(B AS FLOAT) res_cast,
    ROUND(A * CAST(B AS FLOAT), 2) res_cast_round,
    CAST(A as DECIMAL(10, 6)) * CAST(B as DECIMAL(10, 6)) res,
    ROUND(CAST(A as DECIMAL(10, 6)) * CAST(B as DECIMAL(10, 6)), 2) res_round
FROM TMP

Behold working in the SQL Fiddle (if alive). Also put on the Github for future reference.

You cannot use FLOAT

The first two operations are wrong for turning an exact value into approximate value, after all FLOAT is a type of numerical data with decimal part represented in binary and it is impossible to represent all values in binary. This guy does faster bills on the processor, but he can’t be exact. For some types of calculations speed is more important than accuracy, so having an approximate number can be considered correct.

Most programmers don’t know this, but it’s one of the most important things they need to know. And many, even after learning this, think that this "little mistake" does not matter.

I talk about it a lot in other questions:

• How to convert string to double without rounding error?
• Precise mathematics
• What is the correct way to use the float, double and decimal types?

It’s good to know all this so you don’t try solutions with the wrong guy.