How to convert varchar into decimal and its sum

Posted on

Question :

I have the following query:

SELECT SUM(cast(Amount as decimal(10,2))) as result 
FROM tblserviceinvoice

and values of amount (which is varchar) are:

    2.00 
  276,00 
  528.00 
  759.00 
  759.00 
  233.00    
7,956.00 
5,328.00

But as I run the said query, only 2569.00 is given as result. How can I fix it?

Answer :

The issue in the above query is only due to these value (7,956.00 and 5,328.00 ) when casting in implemented on these values the value returned as 7.00 and 5.00 due to which the query gives the result as 2569.00.

Just remove these commas from these numbers and the query will execute properly

SELECT SUM(REPLACE(Amount, ‘,’, ”)) as result
FROM tblserviceinvoice

You could strip the commas from the number strings before converting them to numbers:

SELECT SUM(CONVERT(REPLACE(amt_tran,",",""),DECIMAL(9,2))) as result
FROM tblserviceinvoice

Leave a Reply

Your email address will not be published.