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 Go to next message
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 #334481 is a reply to message #334475] Wed, 16 July 2008 16:08 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: 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 Go to previous messageGo to next message
hnhranch
Messages: 2
Registered: 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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 (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 #334518 is a reply to message #334510] Wed, 16 July 2008 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Typical cowardly disclaimer applies.

Laughing
Re: How to use bankers' rounding in sql [message #334630 is a reply to message #334475] Thu, 17 July 2008 07:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: 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 Go to previous messageGo to next message
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....
Re: How to use bankers' rounding in sql [message #338453 is a reply to message #338436] Tue, 05 August 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
dferris
Messages: 3
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
dferris
Messages: 3
Registered: 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 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

This site shows a function that does this

http://www.tek-tips.com/viewthread.cfm?qid=1487672&page=1
Previous Topic: Compilation Err in Procedure
Next Topic: sysdate
Goto Forum:
  


Current Time: Wed Dec 07 20:26:26 CST 2016

Total time taken to generate the page: 0.07803 seconds