tags:

views:

166

answers:

1

what's the correct approach in t-sql in the following? : sum(rounded(fractions,2)) or round(sum(of fractions),2) ta!

I've tried both approaches and got my total still end up with a different result for each don't know if I need to truncate somwhere

I want x, y * @v = z so that sum(x) * @v = sum(z)

Thanks

+2  A: 
round(sum(of fractions),2)

is most accurate, as you're only rounding once, right at the very end. The other way, you are rounding each individual value first before summing so the end result will more than likely be less accurate all round.

Edit: very basic example:

DECLARE @Data TABLE (Val DECIMAL(9,2))
INSERT @Data VALUES (1.15)
INSERT @Data VALUES (1.15)
INSERT @Data VALUES (1.15)

SELECT ROUND(SUM(Val), 1) As Sum1, SUM(ROUND(Val, 1)) AS Sum2
FROM @Data

gives Sum1=3.50 and Sum2=3.60. 1.15+1.15+1.15 = 3.45 (true total). Therefore, Sum1 is quite obviously more accurate

AdaTheDev