mysql Cumulative sum for each unique ID in each sale:

I have trouble adding and accumulating my amounts per id, I use this solution but it doesn’t work and it adds up all the ids,

id     product_id  quantity  acumulado
168    190         1         1
169    190         3         4
170    195         1         5
171    190         25.60     30.60
172    190         1         31.60

I use this:

SET @csum := 0;

UPDATE sma_sale_items
   SET acumulado = (@csum := @csum + quantity)
 ORDER BY id, product_id;

any solution? ineed this

id     product_id  quantity  acumulado
168    190         1         1
169    190         3         4
170    195         1         1
171    190         25.60     29.60
172    190         1         30.60

For version 5.7 ?

Answer

You can use a SELECT statement containing window analytic function instead of an UPDATE statement :

SELECT id, product_id, quantity, SUM(quantity) OVER (ORDER BY id) AS acumulado
  FROM sma_sale_items

acumulado column shouldn’t need to be stored within a table being a computable column.

Demo1

If you still prefer UPDATE, then use :

UPDATE sma_sale_items s1
 JOIN (
      SELECT id, product_id, SUM(quantity) OVER (ORDER BY id) AS acumulado
        FROM sma_sale_items
       ORDER BY id
      ) s2 ON s1.ID=s2.ID
  SET s1.acumulado = s2.acumulado;

Demo2

UPDATE : Depending on the last edit consider using

SUM(quantity) OVER (PARTITION BY product_id ORDER BY id) AS acumulado

instead of SUM(quantity) OVER (ORDER BY id) AS acumulado

by adding PARTITION BY product_id in order to distinguish the sums grouped by product_id

Demo3