How to sum varchar datatype by converting varchar into float

Question

I have a column 'amount' which is in 'varchar' datatype in my table 'tbl1'. Now I have to calculate total amount of that column (I know I can modify column for my table but I have to follow some procedure to keep it as 'varchar' . but result must be return in float datatype.

I have written following query as:

Select sum(amount) from tbl1;

it gives an error :

Conversion failed when converting varchar datatype into int

so tried this one query also:

select sum(convert(float, amount)) from tbl1;

but I got error.

Answer 1

Try like this:

SELECT sum(CAST(amount AS UNSIGNED)) FROM tbl1

Note that UNSIGNED and SIGNED are used for Integer. If your data may contain decimal values then you can instead try to use decimal like this:

SELECT sum(CAST(amount AS DECIMAL(10,2))) FROM tbl1