Question 192295 in Mysql Question posted in
The official MySQL documentation can be found here.

Sum of total comma separated values in mysql select query

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search