2
id  value
1   1,2,3,4
2   2,3,4

So I want to get this result:

id   sum
1     10
2     9

Can I do it in SQL(MySQL)?

Sato
  • 8,192
  • 17
  • 60
  • 115
  • 8
    Why at the first place do you store your data in such an inconvenient way? – zerkms Feb 05 '14 at 02:50
  • 1
    This is a possible duplicate of [an earlier question](http://stackoverflow.com/q/6311530/2400222). The accepted answer is to create a stored procedure (which is also provided). – Zec Feb 05 '14 at 03:08

2 Answers2

3

With great effort, you can do this. Really, though, this is a very, very bad way to store data.

In the spirit that sometimes we have to use data whose format is not under our control:

select id,
       (substring_index(value, ',', 1) +
        substring_index(substring_index(concat(value, ',0'), ',', 2), ',', -1) +
        substring_index(substring_index(concat(value, ',0'), ',', 3), ',', -1) +
        substring_index(substring_index(concat(value, ',0'), ',', 4), ',', -1) +
        substring_index(substring_index(concat(value, ',0'), ',', 5), ',', -1)
       ) as thesum
from t;

The nested called to substring_index() fetch the nth value in the string. The concat(value, ',0') is to handle the case where there are fewer values than expressions. In this case, the nested substring_index() will return the last value for any value of n greater than the number of items in the list. Concatenating 0 to the list ensures that this doesn't affect the sum.

The SQL Fiddle is here.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

you can do it more dynamically Creating a function. Please follow the following steps

  1. create a function that give the sum of a comma separated value

    CREATE FUNCTION GetToalOfCommaSeperatedVal 
(
  @commaSeperatedVal varchar(100)
)
RETURNS int
AS
BEGIN

    declare @sum int
    DECLARE @x XML 
    SELECT @x = CAST('<A>'+ REPLACE(@commaSeperatedVal,',','</A><A>')+ '</A>' AS XML)

    SELECT @sum=sum(t.value('.', 'int')) 
      FROM @x.nodes('/A') AS x(t)

   return @sum
END
GO

  2. the do a just select command in the following way

    select id,dbo.GetToalOfCommaSeperatedVal(value) from YOUR_TABLE
himadri
  • 626
  • 5
  • 19