mysql cumulative sum of same field value

Question

I have sample data with table name catdog

  | No  | id | data |
      1     1   4000    
      2     2   300    
      3     3   100
      4     1   400
      5     2   30
      6     3   10
      7     1   40
      8     2   3
      9     3   1

I want the result like this:

| No  | id | data | totaldata |
  1     1   4000    4000
  2     2   300     300
  3     3   100     100
  4     1   400     4400      --------> 4000 + 400 on id
  5     2   30      330       --------> 300 + 30 on id
  6     3   10      110       --------> 100 + 10 on id
  7     1   40      4440      --------> 4000 + 400 + 40 on id
  8     2   3       333       --------> 300 + 30 + 1 on id
  9     3   1       111       --------> 100 + 10 + 1 on id

Sum if field No is same. How to write a mysql query for my case?

very very thank you so much GurV

Gurwinder Singh · Accepted Answer · 2017-01-08 05:10:46Z

1

Try this:

select no, id, data, c_data from (
select t1.*,
    @data := case when @id = id then @data + data else data end c_data,
    @id := id
from
(select * from catdog
order by id, No) t1
cross join (select @id := -1, @data := 0) t2) t
order by no;

It uses user defined variables to keep track of sum till now for each id

answered Jan 8, 2017 at 5:10

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql cumulative sum of same field value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related