CODEWITHSUNDEEP
mysqldatabase
Sujit Libi
Sujit Libi

Reputation: 406

How to sum varchar datatype by converting varchar into float

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.

Upvotes: 9

Views: 75527

Answers (3)

lungu
lungu

Reputation: 87

You could also simply do this:

SELECT SUM(amount::REAL) FROM tbl1;

For PostgreSQL.

Upvotes: 0

Rolando Castro
Rolando Castro

Reputation: 1

In my case, I have an error casting because I have values like - into the fields

I found this solution that has different value types How to Sum a varchar(20) Column

CREATE TABLE Foo (
   keycol INT NOT NULL PRIMARY KEY,
   datacol VARCHAR(20));

INSERT INTO Foo VALUES(1, 'a');
INSERT INTO Foo VALUES(2, '10');
INSERT INTO Foo VALUES(3, '5');
INSERT INTO Foo VALUES(4, NULL);

SELECT SUM(
       CASE WHEN datacol NOT LIKE '%[^0-9]%'
            THEN CAST(datacol AS BIGINT)
            ELSE 0
       END) AS total
FROM Foo;

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

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

Upvotes: 19

Related Questions