CODEWITHSUNDEEP
sqloracle-database
Nick
Nick

Reputation: 13

Oracle Sum with $ symbol

I am trying to sum the field which has a $ symbol next to the number. I am using the below code but I get an "Invalid Number" error.

This field is already a VARCHAR2 type.

SELECT FILER_CD,P ORT_ENT_CD, ENT_TYP_CD, 
       TO_CHAR(Sum(VAL_AMT),'$9,999,999.99') AS SUM_AMT
FROM AMT_TABLE
GROUP BY FILER_CD, PORT_ENT_CD, ENT_TYP_CD, VAL_AMT;

Upvotes: 0

Views: 232

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

If column contains $, remove it.

sum(replace(val_amt, '$', '')) as sum_amt

If column is formatted differently (group and/or decimal symbols), you'll have to fix that as well because you can't sum strings. Sample data might help us help you.

Upvotes: 1

Related Questions