SQL sum money with currency

Question

If I want a query that displays the number of customers living in Penang and also the sum of their income, how should I do it?

SELECT COUNT(Cust_state), SUM(Cust_income)
FROM Customer
WHERE Cust_state = ‘Penang’;

This would not work as the values in Cust_income has a preceding $.

EDIT: The datatype for Cust_income is varchar2.

Answer 1

If you are storing the value as a string, then you should fix the data! Don't store numeric values as strings -- you can always add the currency for output purposes.

You can do something like this:

SELECT COUNT(Cust_state),
       SUM(CAST(REPLACE(Cust_income, '$', '') as NUMERIC(20, 4))
FROM Customer
WHERE Cust_state = 'Penang';

Use whatever type is appropriate for the income.

Answer 2

If you apply appropriate format mask to TO_NUMBER function, then you could do as follows (sample data in lines #1 - 4; query begins at line #5):

SQL> with customer (cust_id, cust_income) as
  2    (select 1, '$1000' from dual union all
  3     select 4, '$1600' from dual
  4    )
  5  select sum(to_number(cust_income, '$9999999999')) sum_income
  6  from customer;

SUM_INCOME
----------
      2600

SQL>