How to convert the value of records in a table with data type varchar to integer or decimal so I can get the sum of the value. I use SQL Server 2005.
Say that I have table structure like this:
ID int
Stuff varchar
and the records
ID = 1, Stuff = 32.43
ID = 2, Stuff = 43.33
ID = 3, Stuff = 23.22
Now I want to get the total of column Stuff:
SELECT SUM(convert(decimal(4,2), Stuff)) as result FROM table
But it doesn't work. Please need help!
Table structure...very basic:
create table tabla(ID int, Stuff varchar (50));
insert into tabla values(1, '32.43');
insert into tabla values(2, '43.33');
insert into tabla values(3, '23.22');
Query:
SELECT SUM(cast(Stuff as decimal(4,2))) as result FROM tabla
Or, try this:
SELECT SUM(cast(isnull(Stuff,0) as decimal(12,2))) as result FROM tabla
Working on SQLServer 2008
You are getting arithmetic overflow. this means you are trying to make a conversion impossible to be made. This error is thrown when you try to make a conversion and the destiny data type is not enough to convert the origin data. For example:
If you try to convert 100.52 to decimal(4,2) you will get this error. The number 100.52 requires 5 positions and 2 of them are decimal.
Try to change the decimal precision to something like 16,2 or higher. Try with few records first then use it to all your select.
The reason could be that the summation exceeded the required number of digits - 4. If you increase the size of the decimal to decimal(10,2), it should work
SELECT SUM(convert(decimal(10,2), Stuff)) as result FROM table
OR
SELECT SUM(CAST(Stuff AS decimal(6,2))) as result FROM table
You can use it without casting such as:
select sum(`stuff`) as mySum from test;
Or cast it to decimal:
select sum(cast(`stuff` as decimal(4,2))) as mySum from test;
EDIT
For SQL Server, you can use:
select sum(cast(stuff as decimal(5,2))) as mySum from test;
mysql so that's what I did the test in.