Loading

Thousand decimal point problem

Thousand decimal point problem

Goran Bibic

7y746SQL

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan7y ago
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 Kumar7y ago
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.