Skip to Main Content
  • Questions

  • Rounding - totals of rounded numbers vs. a number

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kari.

Asked: February 07, 2005 - 10:04 am UTC

Last updated: September 15, 2007 - 3:24 pm UTC

Version: 10.1.1

Viewed 1000+ times

You Asked

Hi Tom,

Is this possible to do from a SQL-query.

I have a set of numbers that need to be converted from one currency to another. The sum of the converted values differ slightly from the original number and the difference - as long it is within a limit- should be added to one of the items making the sum. If the limit -lets say .50 is exceeded then an error should be raised.

The example below is a real-life example where the converted amounts should total 23 727.86 but thanks to rounding differences they total 23 727.85. Now the 0.01 should be placed in one of the items below, preferably to the largest/smallest one depending on the sign of the total.


Thank you for your time,

Cheers
Kari

==== EXAMPLE ====

CREATE TABLE T (ORIG NUMBER(10,2), CONV NUMBER(10,2));

INSERT INTO T (ORIG, CONV) VALUES (-90.59 , -67.69);
INSERT INTO T (ORIG, CONV) VALUES (4174.00 , 3118.88);
INSERT INTO T (ORIG, CONV) VALUES (4809.00 , 3593.36);
INSERT INTO T (ORIG, CONV) VALUES (198.00 , 147.95);
INSERT INTO T (ORIG, CONV) VALUES (-198.00 , -147.95);
INSERT INTO T (ORIG, CONV) VALUES (-5.00 , -3.74);
INSERT INTO T (ORIG, CONV) VALUES (87.03 , 65.03);
INSERT INTO T (ORIG, CONV) VALUES (8.00 , 5.98);
INSERT INTO T (ORIG, CONV) VALUES (-16.00 , -11.96);'
INSERT INTO T (ORIG, CONV) VALUES (37.00 , 27.65);
INSERT INTO T (ORIG, CONV) VALUES (1.38 , 1.03);
INSERT INTO T (ORIG, CONV) VALUES (22543.62 , 16844.97);
INSERT INTO T (ORIG, CONV) VALUES (-12.17 , -9.09);
INSERT INTO T (ORIG, CONV) VALUES (-10.01 , -7.48);
INSERT INTO T (ORIG, CONV) VALUES (174.06 , 130.06);
INSERT INTO T (ORIG, CONV) VALUES (-3.34 , -2.5);
INSERT INTO T (ORIG, CONV) VALUES (58.02 , 43.35);

The rate used is 1,338300. The 23 727.86 is a given figure (by the bank, resulting from converting the sum(orig) using the rate [31755/1.338300]. However, the bank doesn't provide the split for each line so that has to be calculated.

and Tom said...

we can do this with analytics rather easily....

getting the "total" diff is pretty easy:

ops$tkyte@ORA9IR2> select orig,
2 orig*1.33830,
3 round(orig*1.33830,2),
4 sum(orig*1.33830) over () - sum(round(orig*1.33830,2)) over () diff
5 from t;

ORIG ORIG*1.33830 ROUND(ORIG*1.33830,2) DIFF
---------- ------------ --------------------- ----------
-90.59 -121.2366 -121.24 .0122
4174 5586.0642 5586.06 .0122
4809 6435.8847 6435.88 .0122
198 264.9834 264.98 .0122
-198 -264.9834 -264.98 .0122
-5 -6.6915 -6.69 .0122
87.03 116.472249 116.47 .0122
8 10.7064 10.71 .0122
1.38 1.846854 1.85 .0122
22543.62 30170.1266 30170.13 .0122
-12.17 -16.287111 -16.29 .0122
-10.01 -13.396383 -13.4 .0122
174.06 232.944498 232.94 .0122
-3.34 -4.469922 -4.47 .0122
58.02 77.648166 77.65 .0122

15 rows selected.

Here, I am finding the "smallest" and "largest" and tagging each with rn1=1 and rn2=1 repectively. Then, we use a case to see if we want to add the negative difference to the smallest or the positive difference to the largest (assuming that is what you want -- else change the logic))

the last columns are just for "debug" and the 1/0 one is to fail the query in the event the difference exceeds your threshold...



ops$tkyte@ORA9IR2> column subed format a15
ops$tkyte@ORA9IR2> column added format a15
ops$tkyte@ORA9IR2> select orig,
2 round(
3 new+(case when rn1=1 and diff < 0 then diff else 0 end)
4 +(case when rn2=1 and diff > 0 then diff else 0 end),2 ) new,
5 case when abs(diff) > 0.50 then 1/0 end err,
6 (case when rn1=1 and diff < 0 then diff||','||new end) subed,
7 (case when rn2=1 and diff > 0 then diff||','||new end) added
8 from (
9 select orig,
10 orig*1.33830,
11 round(orig*1.33830,2) new,
12 sum(orig*1.33830) over () - sum(round(orig*1.33830,2)) over () diff,
13 row_number() over (order by orig) rn1,
14 row_number() over (order by orig desc) rn2
15 from t );

ORIG NEW ERR SUBED ADDED
---------- ---------- ---------- --------------- ---------------
22543.62 30170.14 .0122,30170.13
4809 6435.88
4174 5586.06
198 264.98
174.06 232.94
87.03 116.47
58.02 77.65
8 10.71
1.38 1.85
-3.34 -4.47
-5 -6.69
-10.01 -13.4
-12.17 -16.29
-90.59 -121.24
-198 -264.98

15 rows selected.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to modify the original to cope when there are more than one "set" of numbers

Kari Paukku, March 03, 2005 - 2:15 am UTC

Thank you Tom for a very useful answer.

May I ask your help on enhancing the query to handle a situation where there are more than one group of numbers where the same rule should be applied.

That is if the initial example would be set number 1, let's say in USD, and then an other set, let's say GBP, would be added to the data, what would needed to modify in the query so that the difference would be calculated for each currency rather than the total data.

Kari

Tom Kyte
March 03, 2005 - 7:27 am UTC

you need a conversion factor and mulitply?

Not following you -- why would it be significantly different

round to 2 decimals for a whole number

Simi, September 29, 2005 - 3:49 pm UTC 

SQL> select round(106,2) from dual;

ROUND(106,2)
------------
         106

But I would like  to get the result as 106.00.
Is this possible with a simple SQL.

The actual requirement is on a Number column and its the selling price of a product.
While displaying this Data, it should be rounded to 2 decimal places. But say one  of the  price of a product is stored as a whole number in the database , it  should still be displayed with the whole number concatenated with a decimal point and 2 zeroes.

Tom Kyte
September 30, 2005 - 8:41 am UTC

to get a number formatted as a character string, you use to_char

select to_char( round(106,2), '999.00' ) from dual;




Decode/Case with Numbers

A reader, September 11, 2007 - 9:26 am UTC

Hi Tom,
Apologies if you think this query doesn't relate to this question.
I am working on some currency conversion routine and effectively want to do something like

select trunc(decode(p_target_ccy, 'GBP', exch_rate, 1/exch_rate) * p_amount , 4)
from exch_rate_tab
where ccy_code = p_target_ccy;

Would this be wrong? I'm having a bit of a discussion here saying that since decode is a string function it will fall over and not convert correctly. This has to do with the fact that we have to round to 4 dp.
The suggested way is to get the exch_rate into a local variable and then do the test in plsql and return the converted value this way. I'd like to hear your take on this.

Thanks
Tom Kyte
September 15, 2007 - 3:24 pm UTC

decode is not a string function - decode is overloaded and capable of accepting and returning any of the scalar types.



Numbers.

A reader, August 01, 2009 - 8:12 pm UTC

Hi Tom,

Can I do this with a single query ?

Numbers Between 0 AND 6.99 RETURN 5.99
Numbers Between 7 AND 11.99 RETURN 9.99
Numbers Between 12.00 AND 16.99 RETURN 15.99
Numbers Between 17.00 AND 21.99 RETURN 19.99
Numbers Between 22.00 AND 26.99 RETURN 25.99
Numbers Between 27.00 AND 31.99 RETURN 29.99
Numbers Between 32.00 AND 36.99 RETURN 35.99
....
Numbers Between 97.00 AND 101.99 RETURN 99.99
Numbers Between 102.00 AND 106.99 RETURN 105.99
Numbers Between 107.00 AND 111.99 RETURN 109.99

And so on ...
Regards,


Numbers.

A reader, August 02, 2009 - 12:14 am UTC

Solution

SELECT l ,
CASE
WHEN l=1 THEN 0
ELSE 2+((l-1)*5)
END rango_inferior,
6.99+((l-1)*5) rango_superior,
CASE
WHEN mod(l,2)=1 THEN (6.99+((l-1)*5))-1
WHEN mod(l,2)=0 THEN (6.99+((l-1)*5))-2
END costo
FROM (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL < 100000)
order by 1
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.