mssql 2019

Question

a : select 48.6632 -48.66

a result : 0.0032

b : select sum(48.6632) - sum(48.66)

b result : 0.00

Why are the results of a and b different?

Answer 1

Hi @정우비나전산팀 ,

Welcome to the microsoft TSQL Q&A forum!

I haven't encountered such problems before, so I simply searched, and it seems that there is no relevant information.

From my own understanding, this may be related to the data type of the value returned by sum(). I tried the following code, and it returned the result I expected:

select cast(sum(48.6632) as decimal(6,4))-cast(sum(48.66) as decimal(4,2))  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

When you do a SUM of decimal datatype of precision p and scale s (that is decimal(p,s)) SQL doesn't know how many rows there will be to be summed, so it makes the result with the maximum precision it can while keeping the scale at s, so the result will be decimal(38,s). See the SUM documentation at sum-transact-sql and scroll down to the Return Types section).

So when you do SUM(48.6632), 48.6632 is decimal(6,4) so the result of the SUM will be decimal(38,4), that is it will have 34 digits to the left of the decimal point and 4 to the right.

And when you do SUM(48.66), 48.66 is decimal(4,2) so the result of the SUM will be decimal(38,2), that is it will have 36 digits to the left of the decimal point and 2 to the right.

When you subtract those two, to guarantee a completely accurate result, you would need 36 digits to the left of the decimal (because of the (38,2) number and 4 digits to the left (because of the (38,4) number). But that would would require the result be decimal(40,4) which is not allowed. So SQL must truncate either the digits to the left of the decimal or the right. It chooses to truncate on the right, so it changes the SUM(48.6632) to a decimal(38,2) which truncates the value to 48.66 and when you do the subtraction you get the result of 0.

@EchoLiu-MSFT has the right solution, cast the result of each SUM to the smallest precision and scale that you know will hold the SUM.

Tom