I have the following query:
SELECT SUM(cast(Amount as decimal(10,2))) as result FROM tblserviceinvoice
and values of
amount (which is
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?
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
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