So I have a table with 2 columns - Item and quantity. Column [item] is unique, [quantity] can have the same value. Task: From first table make another table that contains additional column - cumulative_total. This column is adds maximum values of column [quantity], but when cumulative_total reaches 160, you must last_value[cumulative_total] = 160, and last_value [quantity] = 160 - last_value[quantity]. When this condition is done, task is completed.

Table

Expected result:

If it's necessary, download link .csv file (https://anonfiles.com/1855h9Rby5/task_csv)

True result should be looks like this im stopped at moment that im trying to make condition for cumulative_total.

Set @total:= 0;
Select item,
       sum(quantity) over (ORDER BY quantity DESC) as quantity,
       if (@total:= @total   `quantity` > 160,
           @total:=160,
           @total:= @total   `quantity`
           ) as cumulative_total
FROM task

CodePudding user response：

If I understand correctly, you just need to adjust every quantity down by the amount that the cumulative total went over 160, if any. And remove the rows after it reached 160 in an outer query, by looking for quantities that became negative.

select *
from (
  select
    item,
    quantity - greatest(0, sum(quantity) over (ORDER BY quantity DESC) - 160) as quantity,
    least(sum(quantity) over (ORDER BY quantity DESC),160) as cumulative_total
  from task
) cumulative
where quantity > 0

fiddle