Error converting data type varchar to numeric.

[voroojak]

HI
i have a very big problem.
i have a cloumn name Revenue and i have differt types of revenue in it such as A, B, C.
what i want to do is that i want to say that when the Revenue column is A then sum the tola mount and put in in a new column name A when b then again same thing.
this is my code


case when Revenue='A' then Sum(total_amt) else ' ' end as A

but it is giving me and error Error converting data type varchar to numeric.
please help

[ck9663]

HI
i have a very big problem.
i have a cloumn name Revenue and i have differt types of revenue in it such as A, B, C.
what i want to do is that i want to say that when the Revenue column is A then sum the tola mount and put in in a new column name A when b then again same thing.
this is my code


case when Revenue='A' then Sum(total_amt) else ' ' end as A

but it is giving me and error Error converting data type varchar to numeric.
please help

first, why the error.

because your case statement said when revenue = 'A' return the sum of total_amt else return blank...the case statement should return (the then part and the else part) the same datatype or at least can be converted to the same datatype.

try ....else sum(0) end as A, ...or .....else 0 end as A