CODEWITHSUNDEEP
sqlsql-server-2008-r2
ucef
ucef

Reputation: 557

Sum of data in varchar column

i have to sum element in a column(SCENARIO1) that is varchar and contain data like (1,920, 270.00, 0, NULL) but when i try to convert data into int or decimal i get this error : "he command is wrong when converting value "4582014,00" to int type"

here is my request : 

select sum( convert( int, SCENARIO1) )
from Mnt_Scenario_Exercice where code_pere='00000000'

any help please

Upvotes: 2

Views: 21567

Answers (4)

Walter Mitty
Walter Mitty

Reputation: 18940

The problem is due to the fact that the sum function isn't decoding SCENARIO1 as containing a CSV list of numbers. The way the sum function is usually used is to sum a lot of numbers drawn from multiple rows, where each row provides one number.

Try doing it in two steps. In step 1 convert the table into first normal form perhaps by UNPIVOTING. The 1NF table will have one number per row, and will contain more rows than the initial table.

The second step is to compute the sum. If you want more than one sum in the result, use GROUP BY to create groups, and then select a sum(somecolumn). This will yield one sum for each group.

Upvotes: 1

Christian Phillips
Christian Phillips

Reputation: 18759

Try this, I haven't got a way to test yet, but I will test and replace if incorrect. 

SELECT sum(CAST (replace(SCENARIO1, ',', '') AS INT))
FROM   Mnt_Scenario_Exercice
WHERE  code_pere = '00000000';

EDIT: You can use a numeric for the cast if you need 4582014,00 to be 4582014.00

SELECT sum(CAST (replace(SCENARIO1, ',', '.') AS NUMERIC(10,2)))
    FROM   Mnt_Scenario_Exercice
    WHERE  code_pere = '00000000';

Upvotes: 0

zxc
zxc

Reputation: 1526

4582014,00 should be a decimal
try this (I assume that youre query is working) and changed convert(int into decimal)

select sum(convert(decimal(20,2),replace(SCENARIO1, ',', '.'))) from Mnt_Scenario_Exercice where code_pere='00000000'

Upvotes: 2

roman
roman

Reputation: 117380

try this

select sum(cast(replace(SCENARIO1, ',', '.') as decimal(29, 10)))
from Mnt_Scenario_Exercice
where code_pere = '00000000';

If you couldn't convert your '4582014,00' into decimal, there's a chance you have different decimal separator on your server. You could look what it is or just try '.'

Upvotes: 3

Related Questions