Home » SQL & PL/SQL » SQL & PL/SQL » How to use bankers' rounding in sql (Oracle 8i)
How to use bankers' rounding in sql [message #334475] 
Wed, 16 July 2008 14:45 
hnhranch
Messages: 2 Registered: July 2008 Location: East Texas

Junior Member 


How do you implement bankers' rounding method in Oracle 8i sql oppose to the Symmetric Arithmetic Rounding which Oracle defaults too?
Example select round(45.4500,1) from dual results in value 45.5. Bankers rounding should give the value 45.4.





Re: How to use bankers' rounding in sql [message #334508 is a reply to message #334482] 
Wed, 16 July 2008 21:53 
rleishman
Messages: 3727 Registered: October 2005 Location: Melbourne, Australia

Senior Member 


1 select ceil(45.450*power(10,n)0.5)/power(10,n)
2* from (select 1 as n from dual) dual
SQL> /
CEIL(45.450*POWER(10,N)0.5)/POWER(10,N)

45.4
Ross Leishman



Re: How to use bankers' rounding in sql [message #334510 is a reply to message #334475] 
Wed, 16 July 2008 21:59 

Kevin Meade
Messages: 2101 Registered: December 1999 Location: Connecticut USA

Senior Member 


Here is the wiki with some rules for it.
Wiki Banker's Rounding (roundtoeven)
There is no built in function for Banker's Rounding aka. RoundtoEven, at least I have never seen it. You will have to write your own I guess.
Here is an attempt on my part. You test it out and let me know. I am not sure how well it works on negative numbers, or on rounding to the left of the decimal but you should have these test cases in addition to the typical roundings one might do right?
The case expression is my attempt here to do banker's rounding.
I have included the pieces as additional items so you can see the logic (maybe).
Interpreting the rules from wiki:
Do normal rounding unless the remainder after the digit to round = 5 in which case round up if the digit to round is odd otherwise trunc to the digit to round.
Quote:  bankers_round(.0045) = .004
bankers_round(.0055) = .006
bankers_round(.00451) = .005
bankers_round(.00449) = .004

select
&anumber anumber
,&decimal_places decimal_places
,&anumbertrunc(&anumber,&decimal_places) remainder
,(&anumbertrunc(&anumber,&decimal_places))*power(10,&decimal_places+1) adjusted_remainder_to_compare
,(trunc(&anumber,&decimal_places)trunc(&anumber,&decimal_places1))*power(10,&decimal_places) digit_to_round
,mod((trunc(&anumber,&decimal_places)trunc(&anumber,&decimal_places1))*power(10,&decimal_places),2) is_digit_to_round_odd
,case
when (&anumbertrunc(&anumber,&decimal_places))*power(10,&decimal_places+1) = 5 and
mod((trunc(&anumber,&decimal_places)trunc(&anumber,&decimal_places1))*power(10,&decimal_places),2) = 0 then
trunc(&anumber,&decimal_places)
else
round(&anumber,&decimal_places)
end round_to_even
from dual
.
define decimal_places = 4
define anumber = 999.00055
/
define anumber = 999.00045
/
define anumber = 999.000551
/
define anumber = 999.000451
/
If it does not work, then as the line goes, "don't call me... I'll call you". I only did basic testing which I have included here for your convenience. I expect you to test it well if you use it.
Typical cowardly disclaimer applies.
Kevin





Re: How to use bankers' rounding in sql [message #338436 is a reply to message #334475] 
Tue, 05 August 2008 01:35 
dferris
Messages: 3 Registered: August 2008

Junior Member 


er.....Ross' solution doesn't work (unless I am way out here).
I would expect (bankers rounding to 1 d.p.)
45.350 > 45.4
45.449 > 45.4
45.450 > 45.4
45.451 > 45.5
45.550 > 45.6
45.650 > 45.6
what I get is:
45.350 > 45.3 xx
45.449 > 45.4
45.450 > 45.4
45.451 > 45.5
45.550 > 45.6 xx
45.650 > 45.6
something not quite right there....








Goto Forum:
Current Time: Sun Jun 25 19:27:46 CDT 2017
Total time taken to generate the page: 0.09119 seconds
