-1

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!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Rishu Dwivedi
  • 3
  • 2
  • *the properties that have been damaged.* "damage" == CSV instead of separate values or their sum ? – Akina Oct 18 '22 at 20:56
  • 1
    You should store values one per row, not as a comma-separated list, if you want to write SQL expressions that treat them as discrete values. See my answer to [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Oct 18 '22 at 20:57

1 Answers1

0

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; */
Swifty
  • 2,630
  • 2
  • 3
  • 21