Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can I use Aggregate Functions and convertion function togather?

Re: Can I use Aggregate Functions and convertion function togather?

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 4 Jul 2003 06:53:03 -0700
Message-ID: <3722db.0307040553.7baf8de0@posting.google.com>


> Hi, I've a column which the datatype is varchar, but all data store
> there is real number (I know that this is schema design problem, but I
> can't change that)
>
> Now I need to select the average of all record of that column, group
> by some other criteria, I would like to know can I use convertion
> function inside an aggregate function? Is this operation portable?

Certainly:

SQL> desc mytab1

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ROW_ID                                    NOT NULL NUMBER
 ANYCHAR                                            VARCHAR2(20)
 ANYNUM                                             NUMBER

SQL> select * from mytab1;

    ROW_ID ANYCHAR                  ANYNUM
---------- -------------------- ----------
         1 200.25                      200
         2 200.25                      200

Elapsed: 00:00:00.00
SQL> select avg(to_number(anychar)) from mytab1;

AVG(TO_NUMBER(ANYCHAR))


                 200.25

Elapsed: 00:00:00.00
SQL> Daniel Received on Fri Jul 04 2003 - 08:53:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US