CODEWITHSUNDEEP
mysqlsql
Happy Coder
Happy Coder

Reputation: 4682

SUM of amounts with Dollar sign

I need to find out the SUM of values in a column, which is like '$2' and the type is varchar. How can I convert this on the fly find the SUM ?

Upvotes: 2

Views: 3974

Answers (3)

Lajos Veres
Lajos Veres

Reputation: 13725

This will remove the first characters ans summarizes the remaining:

select sum(substring(field,2)) from table

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You are probably better off using MySQL's implicit conversion:

select sum(trim(replace(col, '$', '')) + 0.0)

The silent conversion will include cents. In addition, non-numeric characters are after the number will not cause an error. The trim() will remove leading spaces which could also affect conversion.

Upvotes: 6

Fahim Parkar
Fahim Parkar

Reputation: 31637

This is working query

SELECT SUM(CAST(substring(fieldName,2) AS UNSIGNED)) FROM tableName;

Demo

Upvotes: 1

Related Questions