"SQL Error (1292): Truncated incorrect DOUBLE value: 'unknown'" upon INSERT but not when only SELECTing

Question

Can someone please help me understand why below INSERT query

INSERT INTO ha_archive.pond(last_updated, water)
SELECT DATE(s.last_updated) 'last_updated',
            SUM(s.state) 'water'
    FROM home_assistant.states s
    WHERE s.entity_id = 'sensor.pond_last_refill' AND s.state > 0
    GROUP BY DATE(s.last_updated)
ON DUPLICATE KEY update
    water = VALUES(water);

triggers

SQL Error (1292): Truncated incorrect DOUBLE value: 'unknown'

While the SELECT query below does not?

SELECT DATE(s.last_updated) 'last_updated',
            SUM(s.state) 'water'
    FROM home_assistant.states s
    WHERE s.entity_id = 'sensor.pond_last_refill' AND s.state > 0
    GROUP BY DATE(s.last_updated)

There are occasional 'unknown' strings among float values, and I've tried casting as float, but that doesn't avoid the error.