sqloracle-databasesumvarchar2

SQL sum money with currency

enter image description here

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.

Solution

  • 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.