Summing an array in SQLite
Say you have a table with an array column in SQLite, and want to calculate the sum of that array for each row.
Suppose your table is named my_data in SQLite and looks like:
id | my_list
-----------------
1 | [ 1, 2, 3 ]
2 | [ 4, 5, 6 ]
3 | [ 7, 8, 9 ]
You can sum up the values in the my_list column via the following SQL:
select t1.id, sum(t2.value) from my_list as t1 join json_each((select my_list from my_data where id = t1.id)) as t2 group by id order by id;
It should return the following:
1 | 6
2 | 15
3 | 24
Hope it saves you some time.