Rounding as a result of multiplication

Question

Rounding as a result of multiplication

Navigation

#1 by (5 votes)

6

I have this scenario: - do a multiplication of 2 values that will be rounded to 2 boxes for the table that will be inserted its result.

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, by doing cast in the second value for the multiplication for the first count 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 .

Then, to solve the problem of the second account, simply remove the cast from the second value: 0.011500 * 50.00 = 0.575000 , the rounding is OK, resulting in 0.58 . But in the first count 0.002071 * 84.50 = 0.175000 already comes out with the wrong result and when we round the 0.18 value is OK.

Any idea what it is?

sql sql-server tipagem cast

asked by anonymous 05.01.2016 / 20:58

1 answer

How to reformulate the UI of the radio and checkbox? Why use layout before loading information?

score 5 · Accepted Answer

Specific problem

Since the data is being stored as DECIMAL , I understand that these are monetary values or another type of number that requires accuracy. So the right account is being made with the last two transactions. 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 default rounding behavior in SQL Server. You can use other criteria. The pattern goes up in positive numbers from 5 in the house that should round. See the article on Wikipedia to know more about rounding ( in Portuguese without details ).

The rounding criterion must be part of the business rule. And after this rule is well defined, you have to tailor the code to serve it based on how the language engine is being used.

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

I got a kickball:

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

See running SQLFiddle .

You can not use FLOAT

The first two operations are wrong by transforming an exact value into an approximate value, after all FLOAT is a numeric data type with decimal part represented in binary and it is impossible to represent all values in binary. This type performs faster on the processor, but can not be exact. For some types of calculations speed is more important than accuracy, so having an approximate number can be considered correct.

Most programmers do not know this but it is one of the most important things they need to know. And many, even after learning this, think that this "little bit" does not matter.

I talk a lot about it in other questions:

It's good to know all this so you do not try solutions with the wrong type.