Sum of total comma separated values in mysql select query

Question

I have one table like below query

mysql> select *from DemoTable;

This will produce the following output −

+--------------------+
| ListOfValues       |
+--------------------+
| 20, 10, 40, 50, 60 |
+--------------------+
1 row in set (0.00 sec)

Expected output is below.

+----------+
| TotalSum |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)

I tried below examples but not working any one.

SELECT SUM(replace(ListOfValues, ',', '')) as TotalSum FROM DemoTable;

Here only first value came to in output, sum is not working.

+----------+
| TotalSum |
+----------+
|      20 |
+----------+
1 row in set (0.00 sec)

Can any one help please. I was tried so many examples taken from stackoverflow but no use.

This would be a simple SUM in SQL, if your database was designed properly (see [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) for lots of other reason why your design is problematic). SUM can add up values from different rows, not all from the same field in the same row. — ADyson, Jun 23 '23 at 06:58
Since you tagged PHP, if - some a strange reason - you don't want to redesign your database, you could just get the field out of SQL into PHP, [explode](https://www.php.net/manual/en/function.explode.php) the variable and [sum the resulting array](https://www.php.net/manual/en/function.array-sum.php) — ADyson, Jun 23 '23 at 07:02
Well that won't be easily possible until you design your database properly. This is an [X-Y Problem](http://xyproblem.info/). Study database normalisation. It would have been a good idea to learn about relational database design, _before_ trying to design a relational database!! P.S. if you don't want to use PHP, why did you tag it?? — ADyson, Jun 23 '23 at 07:09
Then redesign your database to not use comma seperated lists in a column. [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462) — RiggsFolly, Jun 23 '23 at 07:10
It looks like there are ways to do this, but they are unpleasant - see [How to sum a comma separated string in SQL?](https://stackoverflow.com/questions/21567706/how-to-sum-a-comma-separated-string-in-sql) for example. It's really all just nasty workarounds for a nasty design. I would strongly advise fixing the root cause of the problem instead - as you'll have seen if you've read that first link I provided, if you manage to solve this particular headache, the bad design will just keep on giving you other problems instead. — ADyson, Jun 23 '23 at 07:11

SelVazi · Answer 1 · 2023-06-23T09:54:15.357

4

As mentioned in comments you should redesign your database to not use comma seperated lists in a column.

If that is not possible, here is a workaround using json_table:

select t.ListOfValues, sum(j.val)
from mytable t
join json_table(
  CONCAT('[', t.ListOfValues, ']'),
  '$[*]' columns (val int path '$')
) j
group by t.ListOfValues

Demo here

If there are duplicated ListOfValues then an auxiliary column can be used to provide a unique sequential number for each row :

with cte as (
  select *, row_number() over( order by ListOfValues) as rn
  from mytable
)
select t.ListOfValues, sum(j.val)
from cte t
join json_table(
  CONCAT('[', t.ListOfValues, ']'),
  '$[*]' columns (val int path '$')
) j
group by t.rn, t.ListOfValues

check demo : https://dbfiddle.uk/gcUbdS0U

edited Jun 23 '23 at 09:54

answered Jun 23 '23 at 07:39

SelVazi

10,028
2
13
29

Good solution. I suggest ``trim(t.ListOfValues)`` – ValNik Jun 23 '23 at 08:24
1

The values are numeric, so `CONCAT('[', t.ListOfValues, ']')` is enough. https://dbfiddle.uk/Lc0pC0Pv – Akina Jun 23 '23 at 08:33
That is true @Akina thanks for the remark – SelVazi Jun 23 '23 at 08:44
@ValNik trim is not needed – SelVazi Jun 23 '23 at 08:44
Just be aware that repeated `ListOfValues` will be grouped together - `(1,2,3), (1,2,3)` will result in one row with sum `12` instead of two rows with sum `6`. You either need a way to distinguish between the rows when grouping, or to use the substring_index method in the [q/a linked](https://stackoverflow.com/questions/21567706/how-to-sum-a-comma-separated-string-in-sql) by ADyson. – user1191247 Jun 23 '23 at 09:34
1

Good remark @user1191247 , in this case an auxiliary column can be used to provide a unique sequential number for each row https://dbfiddle.uk/gcUbdS0U – SelVazi Jun 23 '23 at 09:52

score 1 · Answer 2 · answered Jun 23 '23 at 07:54

You can use prepared statements.

Schema (MySQL v5.7)

CREATE TABLE test (
  str varchar(20)
);
INSERT INTO test VALUES ('1,7,88,56');

set @s=(select * FROM test);
set @t=(select replace(@s,',','+'));
set @u=concat('select ',@t);

prepare st from @u;
execute st;

1+7+88+56
152

View on DB Fiddle

Sum of total comma separated values in mysql select query

2 Answers2