CODEWITHSUNDEEP
phpmysql
No Reply
No Reply

Reputation: 143

PHP Mysql SUM with decimal format

I have a table which has a column : balance_amount of type VARCHAR

Now I'm facing the problem when I want to SUM.

Example:

if I have content column :

125,000.00
170,000.00

It will show just : 295

What I want is : 295,000.00

Upvotes: 1

Views: 2864

Answers (2)

Anigel
Anigel

Reputation: 3437

You will need to remove the thousands separator.

select sum(replace(column,',','')) from table;

Ideally though you should store numbers using an appropriate field definition and just format the numbers for display.

Upvotes: 1

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7668

Here is SQLFiddle

SELECT FORMAT(SUM(CAST(REPLACE(REPLACE(col,',00',''),'.','') AS SIGNED)), 2)
   AS sumOfColumn 
   FROM tab

Upvotes: 1

Related Questions