CODEWITHSUNDEEP
mysqlsqlcumulative-sum
Deepesh
Deepesh

Reputation: 840

calculating cumulative sum and storing it in another column

Hi I want to calculate cumulative sum on column named "percent_sum" and wanted to store it in column cumulative(70) my table name is localbanya_1, here is my table structure

sku_id   new_total percent_sum
  1         12        30 
  2         8         20
  3         9         20
  4         16        10
  5         10        40

and this is what I'm expecting

sku_id   new_total  percent_sum  cumulative(70)   
  1           12       30             30           
  2           8        20             30+20 
  3           9        20             30+20+20
  4           16       10             30+20+20+10
  5           10       40             30+20+20+10

kindly help. thanks in advance.

Upvotes: 2

Views: 292

Answers (2)

sagi
sagi

Reputation: 40481

You can use a correlated query to sum it like this:

SELECT t.sku_id,t.new_total,t.percent_sum,
       (select sum(s.percent_sum) from YourTable s
        where s.sku_id <= t.sku_id) as cumulative
FROM YourTable t

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use a variable:

SELECT sku_id, new_total, percent_sum,
       @s := @s + percent_sum AS cumulative
FROM mytable
CROSS JOIN (SELECT @s := 0) AS var
ORDER BY sku_id

Upvotes: 4

Related Questions