postgres sql - avg on a column defined as character

babcia01 · Sep 19, 2002

If a column is defined as a character but it contains numeric values, can I use avg on such column or I have to do some type of a cast on the column.
Right now I get:
"unable to select an aggregate function avg(character varying"
on
select avg(a) from table;
Thanks a lot
from "Just started to use PostGres SQL"

raisin96 · Oct 13, 2002

hi babcia01,

You'll just have to convert it to a number so that you could use the avg function.

Assuming you have this table:

Table "Test"
Attribute | Type | Modifier
-----------+--------------+----------
a | character(5) |

If you try this statement:

select avg(to_number(a,99999)) from test;

It should give you the average of the rows in it. The 99999 represents the type(9 for numbers i think) and length of your column (5 digits).

Hope this helps.

babcia01 · Oct 31, 2002

Thanks for the answer.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

postgres sql - avg on a column defined as character

babcia01

IS-IT--Management

raisin96

Programmer

babcia01

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor