wish help you to fix your issue To do this kind of non trivial string manipulations, you need to use stored procedures, which, for MySQL, only appeared 6 years ago, in version 5.0. MySQL 4 is now very old, the latest version from branch 4.1 was 4.1.25, in 2008. It is not supported anymore. Most Linux distributions don't provide it anymore. It's really time to upgrade.
CREATE FUNCTION SUM_OF_LIST(s TEXT)
DECLARE res DOUBLE DEFAULT 0;
WHILE INSTR(s, ",") > 0 DO
SET res = res + SUBSTRING_INDEX(s, ",", 1);
SET s = MID(s, INSTR(s, ",") + 1);
RETURN res + s;
mysql> SELECT SUM_OF_LIST("5,2.1") AS Result;
| Result |
| 7.1 |
I wish this help you Here's an approach that works for a simple example of one machine that varies between on and off during the course of one day. It works regardless of whether the machine is in on or off state in the first row.