How to calculate banker's rounding within SQL select statement

[Pindar]

I had a need to be able to calculate banker's rounding directly within a SQL SELECT statement. After searching here and elsewhere on the internet I could not find a solution. So I worked out how to do this and am posting my solution here for others who may have a similar need.

Banker's rounding rounds a terminal 5 to the nearest even number in the next position instead of the next higher number as in conventional rounding. For more on banker's rounding see for example:

http://en.wikipedia.org/wiki/Rounding.

I was able to find a number of suggestions for calculating banker's rounding in a formula that can be called from a SELECT, but not directly within the SELECT itself. Below is a way to do that. This technique could also be used in a formula.

select dec, value,
round(value+((mod(trunc(power(10,dec)*value),2)-sign(value))*power(10,-length(value-trunc(value)))),dec) "Banker's Round"
from
(
select 2 dec, 0 value from dual union all
select -2, 1650 from dual union all
select -2, 1750 from dual union all
select -3, 23500 from dual union all
select -3, 24500 from dual union all
select -2, -1650 from dual union all
select -2, -1750 from dual union all
select -3, -23500 from dual union all
select -3, -24500 from dual union all
select 2, 7.345 from dual union all
select 2, 3.454 from dual union all
select 2, 3.455 from dual union all
select 2, 3.445 from dual union all
select 2, 3.456 from dual union all
select 2, 3.4543 from dual union all
select 2, 3.4546 from dual union all
select 2, 3.4653 from dual union all
select 2, 3.4656 from dual union all
select 2, 3.4643 from dual union all
select 2, 3.4646 from dual union all
select 8, 3.445657545 from dual union all
select 2, 3.445 from dual union all
select 2, 3.435 from dual union all
select 2, 3.425 from dual union all
select 2, 3.415 from dual union all
select 2, 3.405 from dual union all
select 2, 3.395 from dual union all
select 2, -7.345 value from dual union all
select 2, -3.454 from dual union all
select 2, -3.455 from dual union all
select 2, -3.445 from dual union all
select 2, -3.456 from dual union all
select 2, -3.4543 from dual union all
select 2, -3.4546 from dual union all
select 2, -3.4653 from dual union all
select 2, -3.4656 from dual union all
select 2, -3.4643 from dual union all
select 2, -3.4646 from dual union all
select 8, -3.445657545 from dual union all
select 2, -3.445 from dual union all
select 2, -3.435 from dual union all
select 2, -3.425 from dual union all
select 2, -3.415 from dual union all
select 2, -3.405 from dual union all
select 2, -3.395 from dual
)

 

[SantaMufasa]

Pindar,

If I were in your shoes, for the sake of simplicity, I would create an Oracle BANKERS_ROUND function out of your code:

create or replace function bankers_round (val_in number, dec_in number) return number is
begin
     return round(val_in+((mod(trunc(power(10,dec_in)*val_in),2)-sign(val_in))*power(10,-length(val_in-trunc(val_in)))),dec_in);
end;
/

Function created.

...then call the function in this fashion:

select dec, value, bankers_round (value, dec)
from
(
select 2 dec, 0 value from dual union all
select -2, 1650 from dual union all
select -2, 1750 from dual union all
select -3, 23500 from dual union all
select -3, 24500 from dual union all
select -2, -1650 from dual union all
select -2, -1750 from dual union all
select -3, -23500 from dual union all
select -3, -24500 from dual union all
select 2, 7.345 from dual union all
select 2, 3.454 from dual union all
select 2, 3.455 from dual union all
select 2, 3.445 from dual union all
select 2, 3.456 from dual union all
select 2, 3.4543 from dual union all
select 2, 3.4546 from dual union all
select 2, 3.4653 from dual union all
select 2, 3.4656 from dual union all
select 2, 3.4643 from dual union all
select 2, 3.4646 from dual union all
select 8, 3.445657545 from dual union all
select 2, 3.445 from dual union all
select 2, 3.435 from dual union all
select 2, 3.425 from dual union all
select 2, 3.415 from dual union all
select 2, 3.405 from dual union all
select 2, 3.395 from dual union all
select 2, -7.345 value from dual union all
select 2, -3.454 from dual union all
select 2, -3.455 from dual union all
select 2, -3.445 from dual union all
select 2, -3.456 from dual union all
select 2, -3.4543 from dual union all
select 2, -3.4546 from dual union all
select 2, -3.4653 from dual union all
select 2, -3.4656 from dual union all
select 2, -3.4643 from dual union all
select 2, -3.4646 from dual union all
select 8, -3.445657545 from dual union all
select 2, -3.445 from dual union all
select 2, -3.435 from dual union all
select 2, -3.425 from dual union all
select 2, -3.415 from dual union all
select 2, -3.405 from dual union all
select 2, -3.395 from dual
);

DEC      VALUE BANKERS_ROUND(VALUE,DEC)
--- ---------- ------------------------
  2          0                        0
 -2       1650                     1600
 -2       1750                     1800
 -3      23500                    24000
 -3      24500                    24000
 -2      -1650                    -1600
 -2      -1750                    -1800
 -3     -23500                   -24000
 -3     -24500                   -24000
  2      7.345                     7.34
  2      3.454                     3.45
  2      3.455                     3.46
  2      3.445                     3.44
  2      3.456                     3.46
  2     3.4543                     3.45
  2     3.4546                     3.45
  2     3.4653                     3.47
  2     3.4656                     3.47
  2     3.4643                     3.46
  2     3.4646                     3.46
  8 3.44565755               3.44565754
  2      3.445                     3.44
  2      3.435                     3.44
  2      3.425                     3.42
  2      3.415                     3.42
  2      3.405                      3.4
  2      3.395                      3.4
  2     -7.345                    -7.34
  2     -3.454                    -3.45
  2     -3.455                    -3.46
  2     -3.445                    -3.44
  2     -3.456                    -3.46
  2    -3.4543                    -3.45
  2    -3.4546                    -3.45
  2    -3.4653                    -3.47
  2    -3.4656                    -3.47
  2    -3.4643                    -3.46
  2    -3.4646                    -3.46
  8 -3.4456575               -3.4456575
  2     -3.445                    -3.44
  2     -3.435                    -3.44
  2     -3.425                    -3.42
  2     -3.415                    -3.42
  2     -3.405                     -3.4
  2     -3.395                     -3.4

45 rows selected.

Let us know if this simplifies things for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Pindar]

Oops. I should have said function, not formula. Thanks for your comment. I agree it would be simpler. That's why I mentioned the code could also be used in a function. Actually, my client already has a banker's rounding function. However, due to a permission problem my select (a query in Crystal Reports) could not access functions. I put in a request to support to address the permission problem, however sometimes it can take quite awhile for them to get to a ticket. In the meantime this workaround offered the ability to continue with our development and roll out while waiting for support to eventually address the ticket. There may be other situations where creating a function may not be an option.