## Problem with Round

 Author Message mballico Forum Newbie Group: General Forum Members Points: 3 Visits: 26 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 #RedondeoCreate Table #Redondeo (Orden int Identity(1,1),Valores money)Insert into #RedondeoSelect 71374.24 Union Select 16455.92 Union Select 56454.20 Union Select 9495.18 Union Select 23894.20 Select *,Round(Valores*.21,2) as IVAfrom #RedondeoCompute sum(Valores),sum(Round(Valores*.21,2))RESULT = 177673.74; 37311.48Select sum(Valores),round(sum(Valores *.21),2) as IVAfrom #RedondeoRESULT: 177673.74; 37311.49 Luis Cazares SSC-Forever Group: General Forum Members Points: 42594 Visits: 19846 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.`Select sum(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 Sumfrom #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 Forum Newbie Group: General Forum Members Points: 3 Visits: 26 Thanks Luis, I'll try your suggestions mballico Forum Newbie Group: General Forum Members Points: 3 Visits: 26 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 SSChampion Group: General Forum Members Points: 12385 Visits: 37637 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 #RedondeoCREATE TABLE #Redondeo ( Orden INT Identity(1, 1) , Valores MONEY )INSERT INTO #RedondeoSELECT 71374.24 UNIONSELECT 16455.92 UNIONSELECT 56454.20 UNIONSELECT 9495.18 UNIONSELECT 23894.20SELECT Orden , Valores ,round (Valores * 0.21,2) as rndFROM #RedondeoSELECT Orden , Valores ,round (Valores * 0.21,2) as rndinto #testsumFROM #RedondeoSELECT SUM(rnd) AS sumbyrowFROM #testsumSELECT SUM(ROUND(Valores * 0.21, 2)) AS sumrndFROM #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) One Orange Chip Group: General Forum Members Points: 29967 Visits: 19009 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 differenceRounding 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 SSCarpal Tunnel Group: General Forum Members Points: 4148 Visits: 3436 `CREATE TABLE #t (Valores MONEY);GOINSERT INTO #t SELECT 1.1;GO 6SELECT SUM(ROUND(Valores, 0)) AS SumRound, ROUND(SUM(Valores), 0) AS RoundSumFROM #t;DROP TABLE #t;` The SQL Guy @ blogspot@SeanPearceSQLAbout Me Attachments Round Error.PNG (45 views, 4.00 KB)