PRB: Loss of Precision May Occur When You Use SUM or AVG Functions In a Formula with Division

Article translations Article translations
Article ID: 281341 - View products that this article applies to.
This article was previously published under Q281341
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

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).

WORKAROUND

To work around this behavior, use either of these methods:
  • Use a numeric as the divisor. For example:

    DECLARE @Quantity numeric(15,9)
    SET @Quantity = 3
    
    SELECT Round(CAST(Sum(t1.Amount) AS numeric(15,9))/@Quantity, 9) As Cost
    FROM t1
    
    					
  • Store the sum in a variable first, and use the variable in the formula. For example:

    
    declare @quantity numeric (15,9)
    set @quantity = 3
    
    declare @sum numeric (15,9)
    set @sum= (select sum(amount) from t1)
    select @sum/@quantity as cost
    
    					
  • Put an explicit value in a denominator. For example:
    select sum(Amount)/3 from t1
    					

MORE INFORMATION

To reproduce the behavior, run the following query from Query Analyzer:
CREATE TABLE t1 
(
    Amount    numeric(15,9)    
)
GO
INSERT INTO t1( Amount ) VALUES (0.7)
INSERT INTO t1( Amount ) VALUES (1.3)
SELECT Amount FROM t1

/* result (correct, scale = 9)
Amount    
  
----------------- 
.700000000
1.300000000

DECLARE @Quantity numeric(15,9)
SET @Quantity = 3
    
SELECT t1.Amount / @Quantity As Cost
FROM t1
				
Again, the scale and precision are correct in the result.

However, if you use this code

DECLARE @Quantity numeric(15,9)<BR/>
SET @Quantity = 3

SELECT Sum(t1.Amount)/@Quantity As Cost<BR/>
FROM t1
				
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:

select sum(Amount)/3 from t1
GO
select 2.0000000/@quantity

				
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.
declare @quantity numeric (15,9)
set @quantity = 3

declare @sum numeric (15,9)
set @sum= (select sum(amount) from t1)

select @sum/@quantity as cost
				

Properties

Article ID: 281341 - Last Review: October 16, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB281341

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com