question

74942298 avatar image
0 Votes"
74942298 asked TomCooper-6989 answered

mssql 2019

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?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @74942298,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.