| 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: 3728
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: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Here is the wiki with some rules for it.
Wiki Banker's Rounding (round-to-even)
There is no built in function for Banker's Rounding aka. Round-to-Even, 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
,&anumber-trunc(&anumber,&decimal_places) remainder
,(&anumber-trunc(&anumber,&decimal_places))*power(10,&decimal_places+1) adjusted_remainder_to_compare
,(trunc(&anumber,&decimal_places)-trunc(&anumber,&decimal_places-1))*power(10,&decimal_places) digit_to_round
,mod((trunc(&anumber,&decimal_places)-trunc(&anumber,&decimal_places-1))*power(10,&decimal_places),2) is_digit_to_round_odd
,case
when (&anumber-trunc(&anumber,&decimal_places))*power(10,&decimal_places+1) = 5 and
mod((trunc(&anumber,&decimal_places)-trunc(&anumber,&decimal_places-1))*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....
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
