Thousand decimal point problem

Question

0.00/5 (No votes)

See more:

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?

What I have tried:

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

Posted 28-Dec-18 20:56pm

Goran Bibic

Updated 29-Dec-18 18:37pm

Add a Solution

Comments

Richard MacCutchan 29-Dec-18 4:03am

Why are you storing numbers as character types?

Goran Bibic 29-Dec-18 4:06am

I try to solve the problem, men work before me in this company

Richard MacCutchan 29-Dec-18 4:12am

The way to solve it is to use proper numeric types. The time you spend trying to work round someone else's stupidity is usually time wasted. Bite the bullet now and fix the database.

Goran Bibic 29-Dec-18 4:19am

I know. Have half finished solution. Must repair. To add new column with type numeric in sql table? And visual studio...etc...Right?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Serg9n · Answer 1 · 2018-12-29T18:38:00

If you can't change the DB design for a reason, you need just to eliminate ',' from string represantation of numeric

SQL

SUM(TRY_CONVERT(Decimal(10, 2), ISNULL(NULLIF(REPLACE(iznos_sa_pdv,',','' )''), '0')))

Currently TRY_CONVERT() returns NULL for non-covertible string and SUM() ignores NULLs so
values kind of '1,500' are ignored.

OriginalGriff · Answer 2 · 2018-12-28T21:10:00

Solution 1

I'd start by checking your DB data itself - we have no access to it, but I'd suspect that iznos_sa_pdv contains some empty strings.

Try it in SSMS, and when it gives the "wrong" result, start breaking it up a little to find out exactly what it is processing as data.

Posted 28-Dec-18 21:10pm

OriginalGriff

Comments

Goran Bibic 29-Dec-18 3:14am

Not understand my question.
Value is null sql write 0 that is ok
Value is 500.00 sql write 500.00 its ok

Problem is when have values bigger then 999 with separator , 1,456.00

When is sum in sql 500.00+1,456.00 result is 500.00
Separator , is problem

Some help?

OriginalGriff 29-Dec-18 3:24am

Show us the whole data rows that generate the problem, and the version that doesn't.
We can't work with "my data is ok" which is what you are in effect saying...

Goran Bibic 29-Dec-18 3:27am

Thank you. Regards

Wendelius · Answer 3 · 2018-12-28T21:34:00

Solution 2

From what I gather, you store numbers in a string column in the database. In the SUM operation you end up in problems because of the thousand separators.

If this is the problem, I believe that the only reasonable correction would be to change the data type for the column and store the numbers as numbers, decimal perhaps.

Posted 28-Dec-18 21:34pm

Wendelius

Comments

OriginalGriff 29-Dec-18 3:37am

You are probably right - but without his actual data it's hard to tell ... :sigh:

Goran Bibic 29-Dec-18 3:49am

datatype is varchar (255)

Wendelius 29-Dec-18 4:16am

That's what I suspected. So I would suggest using a numeric data type for the column since you store numbers in it. After that you wouldn't need to do conversions and summing items would be easy to do, not to mention performance gain.

Thousand decimal point problem

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us