Thousand decimal point problem

Question

My code is ok, but if have values from 0 to 999 its ok

Exmpl two values 500.00 and 80.00 result is 580.00

That is ok

Exmp if have two values 1,000.00 and 100.00 result is 100.00

Some help?

select kupac as 'Kupac',
SUM(TRY_CONVERT(Decimal(10, 2), ISNULL(NULLIF(iznos_sa_pdv, ''), '0'))) AS 'UKUPNO'
from(select kupac, iznos_sa_pdv from mp_faktura_lista union all select kupac, iznos_sa_pdv from mp_racun_lista) as alltables
group by kupac

Bryian Tan · Answer

Assuming the column iznos_sa_pdv is VARCHAR type, the easiest way is replace the comma with empty string before converting to decimal. select kupac as 'Kupac' , SUM (TRY_CONVERT( Decimal (10, 2), ISNULL ( NULLIF ( REPLACE (iznos_sa_pdv, ',' , '' ), '' ), '0' ))) AS 'UKUPNO' from ( select kupac, iznos_sa_pdv from mp_faktura_lista union all select kupac, iznos_sa_pdv from mp_racun_lista) as alltables group by kupac I also noticed that TRY_CONVERT will return NULL if failed to convert to specified type, the query can be rewritten to something like below, so the output will look like 0.00. Just a thought. select kupac as 'Kupac' , SUM ( ISNULL (TRY_CONVERT( Decimal (10, 2), REPLACE (iznos_sa_pdv, ',' , '' )),0)) AS 'UKUPNO' from ( select kupac, iznos_sa_pdv from mp_faktura_lista union all select kupac, iznos_sa_pdv from mp_racun_lista) as alltables group by kupac

Suraj Kumar · Answer

You can sum value after using Convert as shown below select SUM (M) AS Total FROM ( SELECT CONVERT ( DECIMAL (10,2), '500.00' ) as M union SELECT CONVERT ( DECIMAL (10,2), '80.00' ) as M )a select SUM (M) AS Total FROM ( SELECT CONVERT ( DECIMAL (10,2), '1000.00' ) as M union SELECT CONVERT ( DECIMAL (10,2), '100.00' ) as M )a If not solved your problem the request you to provide some sample data and expected output.

Thousand decimal point problem

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)