Home » SQL & PL/SQL » SQL & PL/SQL » round
round [message #227701] Thu, 29 March 2007 02:07 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Could I round number to Rs.5(say $5)

EX > select balance,round(balance,2) from net;

    orignal           required
---------- ----------------
     589.9            590
     509.9            510
       143            145
       287            290


rzkhan
Re: round [message #227733 is a reply to message #227701] Thu, 29 March 2007 03:58 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Hi

This may help

create table asher (val number(5,2))

insert into asher values(589.9);
insert into asher values(509.9);
insert into asher values(143);
insert into asher values(287);



select * from asher

VAL
-------
589.9
509.9
143
287



the follwoing query finds the result Cool

select case when (mod(ceil(val),5))=0 then ceil(val)
else ceil(val)+ 5-mod(ceil(val),5) end as Result from asher

Result
-------
590
510
145
290



Thanks and regards
Asher
Re: round [message #227739 is a reply to message #227701] Thu, 29 March 2007 04:26 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Do you actually want to round or go up to the next 5 value ? In my book, 287 rounded to the nearest 5 would be 285 rather than 290.

Here are a couple of possibilities that don't involve doing case statements:

select ceil(287 /5) * 5 from dual

-- gives 290

select round(287 *2, -1)/2 from dual

-- gives 285
Re: round [message #227741 is a reply to message #227733] Thu, 29 March 2007 04:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> with generated as
  2  ( select level dummy
  3    from   dual
  4    connect by level <= 20
  5  )
  6  select dummy
  7  ,      round(dummy / 5) * 5
  8  from   generated;

     DUMMY ROUND(DUMMY/5)*5
---------- ----------------
         1                0
         2                0
         3                5
         4                5
         5                5
         6                5
         7                5
         8               10
         9               10
        10               10
        11               10
        12               10
        13               15
        14               15
        15               15
        16               15
        17               15
        18               20
        19               20
        20               20

[Edit: hm, crossed with Cthulhu..]

[Updated on: Thu, 29 March 2007 04:34]

Report message to a moderator

Re: round [message #227742 is a reply to message #227739] Thu, 29 March 2007 04:30 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Hi Cthulhu

I had the same doubt , but from the requirement he has stated i guess he wants the result to be rounded to multiples of 5 in forward direction .

Thanks and regards

Asher
Re: round [message #227748 is a reply to message #227733] Thu, 29 March 2007 04:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's what I came up with:
SQL> WITH yourtable AS
  2   (
  3     SELECT 589.9 yourcolumn, 590 desired FROM dual UNION ALL
  4     SELECT 509.9 yourcolumn, 510 desired FROM dual UNION ALL
  5     SELECT 143   yourcolumn, 145 desired FROM dual UNION ALL
  6     SELECT 287   yourcolumn, 285 desired FROM dual
  7   )
  8  SELECT yourcolumn
  9       , desired
 10       , (ROUND(yourcolumn*2,-1)/2) rounded
 11  FROM yourtable
 12  /

YOURCOLUMN    DESIRED    ROUNDED
---------- ---------- ----------
     589.9        590        590
     509.9        510        510
       143        145        145
       287        285        285
Note: forget the "With" part. Just look at the SELECT.

MHE

[Updated on: Thu, 29 March 2007 04:57]

Report message to a moderator

Re: round [message #227867 is a reply to message #227701] Thu, 29 March 2007 22:18 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
MHE

I need the result rounded to multiples of 5 in forward direction .

Thanks and regards

rzkhan
Re: round [message #227897 is a reply to message #227867] Fri, 30 March 2007 01:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That makes it a lot easier. We can use CEIL for that:

SQL> WITH yourtable AS
  2   (
  3     SELECT 589.9 yourcolumn, 590 desired FROM dual UNION ALL
  4     SELECT 509.9 yourcolumn, 510 desired FROM dual UNION ALL
  5     SELECT 143   yourcolumn, 145 desired FROM dual UNION ALL
  6     SELECT 287   yourcolumn, 290 desired FROM dual UNION ALL
  7     SELECT 300   yourcolumn, 300 desired FROM dual
  8   )
  9  SELECT yourcolumn
 10       , desired
 11           -- base
 12       ,   ceil(yourcolumn/5)*5 rounded_up
 13  FROM yourtable
 14  /

YOURCOLUMN    DESIRED ROUNDED_UP
---------- ---------- ----------
     589.9        590        590
     509.9        510        510
       143        145        145
       287        290        290
       300        300        300

The idea is quite simple: divide by five, round up to the next integer by using ceil and multiply by five again.

MHE
Re: round [message #227910 is a reply to message #227897] Fri, 30 March 2007 02:41 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
nice
thanks
srinivas
Re: round [message #227955 is a reply to message #227701] Fri, 30 March 2007 07:21 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thanks a lot

rzkhan
Previous Topic: Registering external objects in PL/SQL
Next Topic: INSERT DATA USING PROCEDURE IS POSSIBLE (merged)
Goto Forum:
  


Current Time: Sat Dec 03 01:37:17 CST 2016

Total time taken to generate the page: 0.16505 seconds