Home » SQL & PL/SQL » SQL & PL/SQL » How to use bankers' rounding in sql (Oracle 8i)
How to use bankers' rounding in sql Wed, 16 July 2008 14:45
 hnhranch Messages: 2Registered: 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 #334481 is a reply to message #334475] Wed, 16 July 2008 16:08
 joy_division Messages: 4766Registered: February 2005 Location: East Coast USA Senior Member
You could probably use a combination of FLOOR, ROUND, multiplication and division.

In this case:

Multiply by 10, FLOOR it then divide by 10.
Re: How to use bankers' rounding in sql [message #334482 is a reply to message #334475] Wed, 16 July 2008 16:24
 hnhranch Messages: 2Registered: July 2008 Location: East Texas Junior Member
Thanks for tip, I'm guessing however many decimal places you would need to carry would determine what you multiply and divide by?
Re: How to use bankers' rounding in sql [message #334508 is a reply to message #334482] Wed, 16 July 2008 21:53
 rleishman Messages: 3727Registered: 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: 2102Registered: 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
,(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 #334518 is a reply to message #334510] Wed, 16 July 2008 23:17
 Michel Cadot Messages: 65146Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 Quote: Typical cowardly disclaimer applies.

Re: How to use bankers' rounding in sql [message #334630 is a reply to message #334475] Thu, 17 July 2008 07:58
 Kevin Meade Messages: 2102Registered: December 1999 Location: Connecticut USA Senior Member
Once again, Ross shows me up. Nice work their Ross, much better than my brute force method.

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: 3Registered: 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....
Re: How to use bankers' rounding in sql [message #338453 is a reply to message #338436] Tue, 05 August 2008 03:04
 Michel Cadot Messages: 65146Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
subtract 0.001 and round.

Regards
Michel
Re: How to use bankers' rounding in sql [message #338462 is a reply to message #338453] Tue, 05 August 2008 03:22
 dferris Messages: 3Registered: August 2008 Junior Member
to the result you mean?

That still doesn't work:

45.350 --> 45.3
subtract 0.001 => 45.299
round(45.299,1) = 45.3..same - expecting 45.4
Re: How to use bankers' rounding in sql [message #338477 is a reply to message #338462] Tue, 05 August 2008 03:58
 Michel Cadot Messages: 65146Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
This are inconsistencies in what you say or explain why:
45.350 --> 45.4
45.450 --> 45.4

Regards
Michel

Re: How to use bankers' rounding in sql [message #338488 is a reply to message #338477] Tue, 05 August 2008 04:08
 dferris Messages: 3Registered: August 2008 Junior Member
That is the whole point of Bankers Rounding....it rounds to the nearest even number.
That's what this original post was about.
Maybe you should read the following (see section on Round-to-even method)

http://en.wikipedia.org/wiki/Rounding
Re: How to use bankers' rounding in sql [message #338490 is a reply to message #334475] Tue, 05 August 2008 04:10
 scorpio_biker Messages: 154Registered: November 2005 Location: Kent, England Senior Member
Hi,

This site shows a function that does this