0

I have a column in database with name paid_amount that have multiple values with a separator like:

1,2,4,10

I need the sum of these values: sum = 17

select sum(paid_amount) from invoices where ins_no = 2
  • What is your MySQL version? Does the maximum of the amount of "values per field" exists? – Akina Jan 11 at 8:58
  • mysql 5.0 no limit for values – Abdul Basit Jan 11 at 9:01
  • no limit for values It is theory. But in practice the limit must exist (and it can be calculated from the field data type). How much is it? mysql 5.0 I recommend to create stored procedure (to obtain the whole ins_no - sum recordset) or user-defined function (to obtain the sum for specified ins_no value). – Akina Jan 11 at 9:09
  • Plan A: Do it in your application code, where it will be easier. Plan B: Redesign the schema; commalists are "bad". – Rick James Jan 11 at 21:32
0

I'm a SQL Server DBA and i faced this question as a challenge

First I found this function to split the values

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql

With this function a create this procedure below

DELIMITER $$
CREATE PROCEDURE dowhile(IN ID VARCHAR(5))
BEGIN
    select @total := length(replace(paid_amount,',','')) as coluna from invoices where ins_no = ID;

    SET @c := 0;
    SET @t := 0;

  while @c < @total  DO

        select @t := @t +  SPLIT_STR(paid_amount, ',', @c )  as coluna from invoices where ins_no = ID;

        SET @c :=  @c + 1;
    end while;
    select @t;
END

just run this comand

call dowhile(2);

I think this will be enough

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.