February 8, 2006 at 11:29 am
What is the diff between these two statements:
SUM( ROUND(ISNULL(REVENUE, 0) , 0)) 'total',
ROUND(SUM( ISNULL(REVENUE, 0) ), 0) 'total_2'
When I use these two in a statement with a group by clause I'm getting different results
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
February 8, 2006 at 11:48 am
1 rounds before summing, the other sums before rounding.
If you remove decimal places before summing, and the numbers are all positive, then obviously you'll get a different number .
Sum(5.4 + 5.4) = 10.8 rounded this = 11
If you round 1st, you'll be summing 5 + 5 = 10
February 8, 2006 at 12:04 pm
that what i thought. but that is not what is happening. when i use the group by clause with the second line i get incorrect results. if i take the group by out the second line give correct results. The first line always give the correct results.
my guess is that it has something to do with the internal table that SQL is using to process the group by.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
February 8, 2006 at 12:07 pm
You'll need to post the full SQL, including the GROUP BY.
February 9, 2006 at 11:59 am
Each answer is "correct".
An example:
For 10 entries, each 10.10,
your SUM(ROUND(x, 0)) gives 100 as an answer
while ROUND(SUM(x), 0) gives 101 as an answer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply