CODEWITHSUNDEEP
sqloracle-databasesumvarchar2
user12911503
user12911503

Reputation:

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.

Upvotes: 0

Views: 1463

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

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.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 1

Related Questions