Home » SQL & PL/SQL » SQL & PL/SQL » how to round off figure
how to round off figure [message #221751] Wed, 28 February 2007 05:17 Go to next message
annu-agi1
Messages: 17
Registered: August 2006
Location: Pakistan
Junior Member

hi
experts


i would like to round the figure with 10 like

10001 round with 10000
10002 round with 10000
10003 round with 10005
10004 round with 10005
10005 round with 10005
10006 round with 10006
10007 round with 10006
10008 round with 10006
10009 round with 10010
10010 round with 10010


how can i do this .. pleas help me out
Re: how to round off figure [message #221761 is a reply to message #221751] Wed, 28 February 2007 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is there any pattern here?

OK, I (sort of) understand how 3, 4, 5 round to 5.
Why do 6 and 7 round to 6?
And even more mysterious, why does 8 round to 6 while 9 rounds to 10?
Re: how to round off figure [message #221798 is a reply to message #221751] Wed, 28 February 2007 08:48 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
annu-agi1 wrote on Wed, 28 February 2007 05:17
hi
experts


i would like to round the figure with 10 like

10001 round with 10000
10002 round with 10000
10003 round with 10005
10004 round with 10005
10005 round with 10005
10006 round with 10006
10007 round with 10006
10008 round with 10006
10009 round with 10010
10010 round with 10010


how can i do this .. pleas help me out


I see no pattern in your after values, but it all you want to do is round up or ddown to the nearest 10 then do the following.

select round(my_val/10,0)*10 from mytable
Re: how to round off figure [message #221935 is a reply to message #221798] Thu, 01 March 2007 03:03 Go to previous message
Adrian Billington
Messages: 139
Registered: December 2002
Senior Member
This seems to work according to what you have posted...

SQL> with raw_data as (
  2          select rownum + 10000 as r
  3          from   dual
  4          connect by rownum < 10
  5          )
  6  ,    elements as (
  7          select r
  8          ,      trunc(r,-1) as base_number
  9          ,      remainder(r,trunc(r,-1)) as remain
 10          from   raw_data
 11          )
 12  select r as original
 13  ,      base_number + case
 14                         when remain between 0 and 2
 15                         then 0
 16                         when remain between 3 and 5
 17                         then 5
 18                         when remain between 6 and 8
 19                         then 6
 20                         when remain = 9
 21                         then 10
 22                      end as adjusted
 23  from   elements;

  ORIGINAL   ADJUSTED
---------- ----------
     10001      10000
     10002      10000
     10003      10005
     10004      10005
     10005      10005
     10006      10006
     10007      10006
     10008      10006
     10009      10010
     10010      10010

10 rows selected.


So we can try for other size integers and ranges...

SQL> with raw_data as (
  2          select rownum + 1000 as r
  3          from   dual
  4          connect by rownum < 100
  5          )
  6  ,    elements as (
  7          select r
  8          ,      trunc(r,-1) as base_number
  9          ,      remainder(r,trunc(r,-1)) as remain
 10          from   raw_data
 11          where  r between 1040 and 1050
 12          )
 13  select r as original
 14  ,      base_number + case
 15                         when remain between 0 and 2
 16                         then 0
 17                         when remain between 3 and 5
 18                         then 5
 19                         when remain between 6 and 8
 20                         then 6
 21                         when remain = 9
 22                         then 10
 23                      end as adjusted
 24  from   elements;

  ORIGINAL   ADJUSTED
---------- ----------
      1040       1040
      1041       1040
      1042       1040
      1043       1045
      1044       1045
      1045       1045
      1046       1046
      1047       1046
      1048       1046
      1049       1050
      1050       1050

11 rows selected.


SQL> with raw_data as (
  2          select rownum + 1234000 as r
  3          from   dual
  4          connect by rownum < 100
  5          )
  6  ,    elements as (
  7          select r
  8          ,      trunc(r,-1) as base_number
  9          ,      remainder(r,trunc(r,-1)) as remain
 10          from   raw_data
 11          where  r between 1234050 and 1234060
 12          )
 13  select r as original
 14  ,      base_number + case
 15                         when remain between 0 and 2
 16                         then 0
 17                         when remain between 3 and 5
 18                         then 5
 19                         when remain between 6 and 8
 20                         then 6
 21                         when remain = 9
 22                         then 10
 23                      end as adjusted
 24  from   elements;

  ORIGINAL   ADJUSTED
---------- ----------
   1234050    1234050
   1234051    1234050
   1234052    1234050
   1234053    1234055
   1234054    1234055
   1234055    1234055
   1234056    1234056
   1234057    1234056
   1234058    1234056
   1234059    1234060
   1234060    1234060

11 rows selected.


Regards
Adrian
Previous Topic: ORA-29279:SMTP permanent error:553 sorry , that domain isn,t in my list of allowed rcpthosts
Next Topic: Accumulate sum until reaching and *exact* required value ?
Goto Forum:
  


Current Time: Sat Dec 03 16:31:51 CST 2016

Total time taken to generate the page: 0.04844 seconds