May 30, 2012 at 8:52 am
Rounding percentages and summing them up will not give correct results all the time due to the rounding. Is there a way in SQL to round the number(s) so that the total adds up correctly?
Note that I have to round value for each individual record since the result will be grouped in such a way and can not round at the end.
Eg1:
105 * 25% = 26.25 => Rounded as 26
105 * 25% = 26.25 => Rounded as 26
105 * 25% = 26.25 => Rounded as 26
105 * 25% = 26.25 => Rounded as 26
Total should be 105 but the SUM(rounded values) = 104 which is incorrect.
Eg2:
1 * 25% = .04 => Rounded as 0
1 * 25% = .04 => Rounded as 0
1 * 25% = .04 => Rounded as 0
1 * 25% = .04 => Rounded as 0
Total should be 1 but the SUM(rounded values) = 0 which is incorrect.
Trying for some kind of logic that can round first three to 26 and last one to 27 in Eg1 and can round first three to 0 and last one to 1 in Eg2?
Any ideas would be appreciated.
May 30, 2012 at 9:06 am
look at using a union to get your sum.
create table #tmp (num int)
insert into #tmp
values(105)
go 4
SELECT cast(round(num*.25,0) as int)
FROM #tmp
union all
select cast(SUM(num*.25) as int)
FROM #tmp
May 30, 2012 at 9:19 am
Please post the code you are using for calculating these numbers. It helps me if I have a visual of what you're doing.
May 30, 2012 at 9:40 am
Rounding in itself is a business decision. As in- it's usually a design constraint imposed by the byusiness on how it SHOULD work. No matter how you look at it, it's something that removes precision, so the business stakeholder needs to approve or decided on when and how that's appropriate. I've been in several businesses with multiple specific requirements as to "who" gets the adjusting entry (largest lineitem or specific journal entry, etc...).
That said - this will essentially do the "adjusting" based on the example you showed (ex1 that is - the math in ex2 makes no sense). Putting the "extra" in the last entry is difficult, since 1. there's no physical ordering in the DB, and 2. you don't necessarily know how many parts you might have.
So - while this will do the job it likely will start suffering performance-wise on any large scall application. You will likely have to stage this to a work table if you need to scale this up.
;with Dummydata as (
select 1 as groupcol, 1 as groupOrderingCol, 105.0 as amt, .25 as proration
union all
select 1 as groupcol, 5 as groupOrderingCol, 105 as amt, .25 as proration
union all
select 1 as groupcol, 10 as groupOrderingCol, 105 as amt, .25 as proration
union all
select 1 as groupcol, 34 as groupOrderingCol, 105 as amt, .25 as proration
union all
select 2 as groupcol, 100 as groupOrderingCol, 345 as amt, .25 as proration
union all
select 2 as groupcol, 220 as groupOrderingCol, 345 as amt, .25 as proration
union all
select 2 as groupcol, 392 as groupOrderingCol, 345 as amt, .25 as proration
union all
select 2 as groupcol, 400 as groupOrderingCol, 345 as amt, .25 as proration
union all
select 3 as groupcol, 100 as groupOrderingCol, 344 as amt, .25 as proration
union all
select 3 as groupcol, 220 as groupOrderingCol, 344 as amt, .25 as proration
union all
select 3 as groupcol, 392 as groupOrderingCol, 344 as amt, .25 as proration
union all
select 3 as groupcol, 400 as groupOrderingCol, 344 as amt, .25 as proration),
WorkingProration as (
select ROW_NUMBER() over (PARTITION by groupcol order by groupOrderingCol desc) revsequence,
SUM(round(amt*proration,0)) over (PARTITION by groupcol) roundedDetail,
round(SUM(amt) over (partition by groupcol) * proration,0) roundedSum,* from Dummydata)
select case when revsequence=1 then roundedSum-roundedDetail else 0 end + round(amt*proration,0) as RoundedWithAdjusting,
* from WorkingProration
order by groupcol, grouporderingcol
----------------------------------------------------------------------------------
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?
May 30, 2012 at 9:41 am
Hope this helps. The column alignment is collapsed in the tables below. Pls refer the attachment for clear table structure.
tblCount
count_seq count
1 105
tblPercentage
percent_seq count_seq code percentage
1 1 XX 50
2 1 YY 50
SELECT per.code, ROUND(SUM(cnt.count* per.percentage/100.00),0) AS count
FROM tblCount cnt
INNER JOIN tblPercentage per ON cnt.count_seq = per.count_seq
GROUP BY code
Getting a result of
Code Count
XX 53
YY 53
Total = 106
I am expecting a result of
Code Count
XX 53
YY 52
Total = 105
May 30, 2012 at 10:34 am
In your examples, there isn't a single instance of a summed amount that comes to a decimal total above 50. In normal rounding, anything below 50 rounds down to the nearest whole number and everything above 50 rounds up. The "50" itself can go either way, depending on the business requirements, but it appears as if SQL is treating it as a "round up" scenario.
Here's what I've been playing with:
CREATE TABLE #TempCount (count_seq INT IDENTITY(1,1) NOT NULL, CountVal INT);
INSERT INTO #TempCount (CountVal)
VALUES (105),(102),(103),(70);
CREATE TABLE #TempPercentage (Percent_seq INT IDENTITY(1,1) NOT NULL, count_seq INT,
code CHAR(2), Percentage INT);
INSERT INTO #TempPercentage (count_seq, code, Percentage)
VALUES (1, 'XX', 50),
(1, 'YY', 50),
(2, 'AA', 25),
(3, 'BB', 75),
(4, 'BB', 75);
SELECT per.code, ROUND(SUM(cnt.CountVal* per.percentage/100.00),0) AS CountVal,
SUM(cnt.CountVal* per.percentage/100.00) as SummedTot,
SUM(cnt.CountVal* (per.percentage/100.00) ) AS TryThisWay1,
SUM((cnt.CountVal* per.percentage)/100.00 ) AS TryThatWay2
FROM #TempCount cnt
INNER JOIN #TempPercentage per
ON cnt.count_seq = per.count_seq
GROUP BY code
DROP TABLE #TempCount;
DROP TABLE #TempPercentage;
EDIT: It looks as if you want those extra decimal bits rounded up, you'll have to force the issue. Or find a different way of doing your SUMs to total the numbers, then do a ROUND on the total outside of that particular SELECT statement.
May 31, 2012 at 1:35 pm
Yes, I understand your concern, But the requirement needs it to be rounded.
The solution recommended by Matt Miller might work for me and I am customizing it based on the design. But still have to get over some hurdles before getting the final result. Thanks everyone for the help.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy