SQL row column sum with spaces between numbers

Question

mysql table:

table_results:

id  results

1   1 0 1 2 4 5
2   5 4 2 6  
3   7 2 8 2 4

I'm creating a Yii gridview I need to

SELECT SUM(results) AS results2 FROM table_results WHERE id = 1:

for example I have to sum first row: 1+0+1+2+4+5 and make as results2 which = 15.

How to do that just in sql?

Never store data as space/comma separated items in a column, you will get so much trouble... One row/value is the SQL way! — jarlh, Oct 07 '15 at 14:45
When you generate the results, generate the sum. And, as a separate column. That would seem to be the sanest approach. — Gordon Linoff, Oct 07 '15 at 14:53
You really don't want to store data like this. However, in the event that you need to do it anyway, look at this question for splitting multiple values out of a field: http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query — , Oct 07 '15 at 14:53
here a [similar post](http://stackoverflow.com/questions/2341374/sql-comma-delimted-column-to-rows-then-sum-totals) — jedi, Oct 07 '15 at 14:58

Lukasz Szozda · Answer 1 · 2015-10-07T21:05:36.677

There is no need for dynamic SQL. You can use:

SqlFiddleDemo

SELECT sub.id,  SUM(sub.val) AS `sum`
FROM
(
  SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(t.results, ' ', n.n), ' ', -1) AS val
  FROM table_results t 
  CROSS JOIN 
  (
     SELECT a.N + b.N * 10 + 1 n
       FROM 
      (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
     ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  ) n
  WHERE n.n <= 1 + (LENGTH(t.results) - LENGTH(REPLACE(t.results, ' ', '')))
) sub
-- WHERE sub.id = 1
GROUP BY sub.id

Gordon Linoff · Answer 2 · 2015-10-07T15:03:14.177

1

One way to do this is using dynamic SQL. So, entirely within MySQL:

declare @sql varchar(2000);

select @sql = concat('select ', replace(results, ' ', '+'))
from table_results tr
where id = 1;

prepare s from @sql;
execute s;

deallocate prepare s;

As a note: Just because I answered the question does not mean that I condone the data structure.

edited Oct 07 '15 at 15:03

answered Oct 07 '15 at 14:55

Gordon Linoff

1,242,037
58
646
786

I run your code http://sqlfiddle.com/#!2/e756a/1/3 and for one row it is ok. But for multiple rows I will get multiple resultsets http://sqlfiddle.com/#!9/c7a372/1 – Lukasz Szozda Oct 07 '15 at 16:12
@lad2025 . . . The question is only about summing the values in one row: "I have to sum first row". – Gordon Linoff Oct 07 '15 at 19:40
I slightly extend your idea to work for all rows: http://sqlfiddle.com/#!9/c7a372/14/4 I will upvote tomorrow, because I've used all my votes ;) – Lukasz Szozda Oct 07 '15 at 20:59

SQL row column sum with spaces between numbers

2 Answers2