sql query to perform SUM on String data type

User_X9S6M

In a table have three columns of data type string with their values as decimals. Need sql query to perform SUM for these columns.

SALES_QTY           SALES_REVENUE_SETTING_CURRENCY  SALES_REVENUE_SITE_CURRENCY

6679642.000000000    128889.721766052                        14520998.854682000

6.000000000          40092.211517913                         264187.628566338

can someone help?

Frank Kulash

Hi, @User_X9S6M

In a table have three columns of data type string with their values as decimals. 

Why are you storing them as strings? Wouldn't it make more sense to use NUMBER columns?

SUM only works on NUMBERs. If you're stuck with numbers stored in string columns, you can use TO_NUMBER to convert them into NUMBERs, then do the SUM, for example

SUM (TO_NUMBER (sales_qty DEFUALT NULL ON CONVERSION ERROR))

depending on your Oracle version.

I hope this answers your question. If not, post a little sample data (CREATE TABLE and INSERT statements), the exact results you want from that data, and an explanation of how you get those results from that data. Alwyas say which version of Oracle you're using (e.g., 12.2.0.1.0),