CODEWITHSUNDEEP
mysqlsqlcumulative-sum
Bia
Bia

Reputation: 183

Cumulative Sum of column doesn't work

I'm trying to do a cumulative sum of column due_pis, using this:

set @csum := 0;
select SUM(e.vl_taxe) as vl_taxe, 
SUM(CASE WHEN e.hold_pis = 0 THEN e.due_pis ELSE 0 END) as total, 
(@csum := @csum + SUM(CASE WHEN e.hold_pis = 0 THEN e.due_pis ELSE 0 END)) as cumulative_sum
FROM taxes as e 
WHERE e.id_enterprise = 1 
AND (e.dt_taxe BETWEEN '2016-12-01' AND '2017-04-01' ) AND lg_cancel = 0 
GROUP BY e.dt_taxe

The result is:

vl_taxe     total   cumulative_sum

24850.76    1.37     1.37
31444.64    1.26     1.26
32329.27     0        0
33654.82    1.37     1.37
39682.94     0        0

But, I want obtain this:

vl_taxe     total   cumulative_sum

24850.76    1.37     1.37
31444.64    1.26     2.63
32329.27     0       2.63
33654.82    1.37     4
39682.94     0       4

Upvotes: 0

Views: 107

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Get the summed values for each date and then specify an order by to get the desired cumulative sum.

set @csum := 0;

select dt_taxe,
vl_taxe,
total,
@csum := @csum + total as cumulative_sum
from (select e.dt_taxe,
      SUM(e.vl_taxe) as vl_taxe, 
      SUM(CASE WHEN e.hold_pis = 0 THEN e.due_pis ELSE 0 END) as total
      FROM taxes as e 
      WHERE e.id_enterprise = 1 
      AND (e.dt_taxe BETWEEN '2016-12-01' AND '2017-04-01') 
      AND e.lg_cancel = 0 
      GROUP BY e.dt_taxe
     ) t 
ORDER BY dt_taxe

Upvotes: 2

Related Questions