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?
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?
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
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.
16 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index