8

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Sujit Libi
  • 386
  • 1
  • 4
  • 16

3 Answers3

18

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
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
0

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;
Rolando Castro
  • 1
  • 1
0

You could also simply do this:

SELECT SUM(amount::REAL) FROM tbl1;

For PostgreSQL.

lungu
  • 47
  • 2
  • 10