Article ID: 281341 - View products that this article applies to.
This article was previously published under Q281341
When you use the SUM or AVG aggregate function with division, the result loses precision. The loss of precision occurs regardless of whether you use a numeric or decimal datatype.
This behavior is due to the nature of decimal and numeric datatypes and the truncation that occurs as a result of the precision and the scale values. According to SQL Server Books Online, the result of sum(numeric(p,s)) is numeric(38,s). In the preceding case, sum(amount) is numeric(38,9). When you have an expression of numeric(38,9) / numeric(15,9); ideally, the result is numeric(63,25). However, because the maximum precision is 38, the result truncates to (38,6).
To work around this behavior, use either of these methods:
To reproduce the behavior, run the following query from Query Analyzer:
Again, the scale and precision are correct in the result.
However, if you use this code
the result is .666666.
The loss of precision does not occur if you provide an explicit value in the denominator. Loss of precision does not occur if you put in the value of the sum:
No loss of precision occurs if you store SUM(amount) in a variable and you then use the variable. The loss of precision only occurs when you use the aggregate keyword SUM or AVG in a mathematical formula with division.