Problem with Round

Question

Problem with Round

mballico

Grasshopper

Points: 21
More actions
July 23, 2014 at 10:57 am

#309443

I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values for the percentage (also using round).
IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo
Create Table #Redondeo (Orden int Identity(1,1),Valores money)
Insert into #Redondeo
Select 71374.24 Union Select 16455.92
Union Select 56454.20 Union Select 9495.18
Union Select 23894.20
Select *,Round(Valores*.21,2) as IVA
from #Redondeo
Compute sum(Valores),sum(Round(Valores*.21,2))
RESULT = 177673.74; 37311.48
Selectsum(Valores),round(sum(Valores *.21),2) as IVA
from #Redondeo
RESULT: 177673.74; 37311.49

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

The problem is that one query is summing the values before rounding and the other one is summing the values after rounding. You need to define which option do you want.

This should make it clear.

Selectsum(Valores),

sum(round(Valores *.21,2)) as IVA, --Round then sum

Round(sum(Valores*.21),2) AS IVA2, --Sum then round

sum(Valores*.21) AS IvaSinRedondear --Just Sum

from #Redondeo

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mballico Grasshopper Points: 21 More actions · Answer 2

mballico

Grasshopper

Points: 21

July 23, 2014 at 12:20 pm

#1731897

Thanks Luis, I'll try your suggestions

mballico Grasshopper Points: 21 More actions · Answer 3

To clarify the problem. The initial calculation is: Round (177673.74 * 0.21,2) = 37311.49

Then I need to make the opening of each item and the corresponding calculation, which must match the sum to the initial calculation, but it gives 0.01 difference:

Ítems; Valores; Round (Valores*0.21,2)

Item 1; 9,495.18; 1,993.99

Item 2 16,455.92 3,455.74

Item 3 23,894.20 5,017.78

Item 4 56,454.20 11,855.38

Item 5 71,374.24 14,988.59

37,311.48

But I think there is no solution can only be set in any of the items the difference

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

just a thought...but appears to work, but maybe I am not entirely clear on the issue

IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL

DROP TABLE #Redondeo

CREATE TABLE #Redondeo (

Orden INT Identity(1, 1)

, Valores MONEY

)

INSERT INTO #Redondeo

SELECT 71374.24 UNION

SELECT 16455.92 UNION

SELECT 56454.20 UNION

SELECT 9495.18 UNION

SELECT 23894.20

SELECT Orden

, Valores

,round (Valores * 0.21,2) as rnd

FROM #Redondeo

SELECT Orden

, Valores

,round (Valores * 0.21,2) as rnd

into #testsum

FROM #Redondeo

SELECT SUM(rnd) AS sumbyrow

FROM #testsum

SELECT SUM(ROUND(Valores * 0.21, 2)) AS sumrnd

FROM #Redondeo

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

mballico (7/23/2014)
To clarify the problem. The initial calculation is: Round (177673.74 * 0.21,2) = 37311.49
Then I need to make the opening of each item and the corresponding calculation, which must match the sum to the initial calculation, but it gives 0.01 difference:
Ítems; Valores; Round (Valores*0.21,2)
Item 1; 9,495.18; 1,993.99
Item 2 16,455.92 3,455.74
Item 3 23,894.20 5,017.78
Item 4 56,454.20 11,855.38
Item 5 71,374.24 14,988.59
37,311.48
But I think there is no solution can only be set in any of the items the difference

Rounding errors will occur in any scenario like this. It's not that SQL Server's ROUND() function has an issue, the use of rounding lends itself to rounding errors.

http://www.investopedia.com/terms/r/rounding-error.asp

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

CREATE TABLE #t (Valores MONEY);

GO

INSERT INTO #t SELECT 1.1;

GO 6

SELECT

SUM(ROUND(Valores, 0)) AS SumRound,

ROUND(SUM(Valores), 0) AS RoundSum

FROM

#t;

DROP TABLE #t;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]