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

From: Stephen_CA <stephen.bell_at_sympatico.ca>
Date: 4 Jul 2003 05:39:54 -0700
Message-ID: <5aeee507.0307040439.13f0efa3@posting.google.com>

carfield_at_carfield.com.hk (Carfield Yim) wrote in message news:<d817c73e.0307040257.3fa51979_at_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?

Hi,

Sure, you can do that. As an example, I created a table called CONVERT with one column called VCHAR_WITH_NUMBER. I inserted ten records as strings, from '1' to '10'. Then I used the DUMP function to confirm the datatype.

SQL> select dump(avg(to_number(vchar_with_nbr))) from convert;

DUMP(AVG(TO_NUMBER(VCHAR_WITH_NBR)))

---

Typ=2 Len=3: 193,6,51

The "Typ=2" confirms the datatype is NUMBER..here's the actual operation:

SQL> select avg(to_number(vchar_with_nbr)) from convert;

AVG(TO_NUMBER(VCHAR_WITH_NBR))

---

                           5.5

So, it works..just add your group by and whatever else you need and you're set.

Steve Received on Fri Jul 04 2003 - 07:39:54 CDT