How to find the sum of all the numeric values present in a MySQL table's column which has datat-CodePudding

I have a table which has been created using the following query

create table damaged_property_value 
(case_id int, property_value varchar(100) );

insert into damaged_property_value (1,'2000'),(2,'5000,3000,7000');

The problem is I need to find the total value of all the properties that have been damaged.

I am writing the following query to return the sum:

select SUM(cast(property_value as unsigned)) from damaged_property_value;

It returns the sum as 7000, i.e , 2000 5000. It is not considering the value of property which are separated by commas.

Note that 5000,3000 and 7000 are values of three different properties that have been damaged in a particular case. It should have produced 17000 as an answer.

How to solve this problem.

Please help!

CodePudding user response：

As was said, the best solution would be to fix the data structure. Now, just for the fun of solving the problem, and after much research, I managed to do the following (it requires the case_id to be sequential, starting at 1) that calculates the values of the property_value strings and puts them into the new actual_value field.

drop table if exists damaged_property_value;
create table damaged_property_value
(case_id int primary key, property_value varchar(100), actual_value int );

insert into damaged_property_value (case_id, property_value) values (1,'2000'),(2,'5000,3000,7000'),(3, '7000, 2000'),(4, '100,200,300,400,500,600');


drop procedure if exists Calculate_values;
DELIMITER $$ 
CREATE PROCEDURE Calculate_values()
 BEGIN
DECLARE count INT;
  SET count = 1;
  label: LOOP
select 
concat('update damaged_property_value set actual_value = ',
replace((select property_value from damaged_property_value where case_id = count), ",", " "),
' where case_id = ', count, ';')
into @formula;

#select @formula;
prepare stmt from @formula;
execute stmt;
deallocate prepare stmt;
    SET count = count  1;
    IF count > (select count(*) from damaged_property_value)  THEN
     LEAVE label;
    END IF;
 END LOOP label;
END $$
DELIMITER ;

CALL Calculate_values();

select * from damaged_property_value;

/* select SUM(actual_value) from damaged_property_value; */