CODEWITHSUNDEEP
mysqlsql
user255512
user255512

Reputation: 13

mysql cumulative sum of same field value

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

Upvotes: 1

Views: 104

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

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

Upvotes: 1

Related Questions