CODEWITHSUNDEEP
mysqlsqlgroup-bysumwindow-functions
Julio Moyano Basso
Julio Moyano Basso

Reputation: 105

How to calculate reverse running sum in MySQL

I have this query where I calculated cumulative sum. Now, I need to calculate reverse cumulative sum for the same variable

SELECT t1.date, t1.ant, t1.hab,  
(@csum:= @csum + t1.hab) as cumulative_hab
from(
SELECT date,
        ant, 
        sum(num_habit) as hab
        from xxxxxxxxxx
        WHERE date BETWEEN CURDATE() - INTERVAL 5 DAY AND CURDATE()
        group by ant) AS t1
,(select @csum := 0) vars
order by t1.ant

My table look like this

date       ant   hab    cumulative_hab
24-05-2020  0   382,000   382,000
24-05-2020  1   28,000    410,000
24-05-2020  2   26,000    436,000
24-05-2020  3   11,000    447,000
24-05-2020  4   29,000    476,000
24-05-2020  6   6,000     482,000
24-05-2020  7   12,000    494,000
28-05-2020  8   50,000    544,000
24-05-2020  12  5,000     549,000
24-05-2020  13  6,000     555,000

I would like another column with reverse running sum (reverse cumulative sum), the first value is calculated 555 - 382

 date      ant   hab    cumulative_hab   reverse_cum_hab
24-05-2020  0   382,000   382,000             555,000
24-05-2020  1   28,000    410,000             173,000, 
24-05-2020  2   26,000    436,000             145,000
24-05-2020  3   11,000    447,000             119,000
24-05-2020  4   29,000    476,000             108,000
24-05-2020  6   6,000     482,000              79,000
24-05-2020  7   12,000    494,000              73,000
28-05-2020  8   50,000    544,000              61,000
24-05-2020  12  5,000     549,000              11,000
24-05-2020  13  6,000     555,000               6,000

Upvotes: 2

Views: 1333

Answers (1)

GMB
GMB

Reputation: 222432

As a starter: if you are running MySQL 8.0, you can do this easily with window functions:

select 
    date,
    ant,
    sum(num_habit) as hab,
    sum(sum(num_habit)) over(order by date) cumulative_hab,
    sum(sum(num_habit)) over(order by date desc) reverse_cumulative_hab
from mytable
where date between current_date - interval 5 day and current_date
group by date, ant
order by date

In earlier versions, it is more complicated. I would suggest joining two queries:

select t.*, r.reverse_cumulative_hab
from (
    select t.*, @csum := @csum + hab cumulative_hab
    from (
        select date, ant, sum(num_habit) as hab
        from mytable
        where date between current_date - interval 5 day and current_date
        group by date, ant
        order by date
    ) t
    cross join (select @csum := 0) x
) t
inner join (
    select t.*, @rcsum := @rcsum + hab reverse_cumulative_hab
    from (
        select date, ant, sum(num_habit) as hab
        from mytable
        where date between current_date - interval 5 day and current_date
        group by date, ant
        order by date desc
    ) t
    cross join (select @rcsum := 0) x
) r on r.date = t.date
order by t.date

This assumes no duplicate ant per date.

It might also be possible to simplify the logic and compute the reverse sum by taking the difference between the cumulative sum and the overall sum:

select t.*, z.total_hab - t.cumulative_hab reverse_cumulative_hab
from (
    select t.*, @csum := @csum + hab cumulative_hab
    from (
        select date, ant, sum(num_habit) as hab
        from mytable
        where date between current_date - interval 5 day and current_date
        group by date, ant
        order by date
    ) t
    cross join (select @csum := 0) x
) t
cross join (
    select sum(num_habit) as total_hab
    from mytable
    where date between current_date - interval 5 day and current_date
) z
order by date

Note that these queries are safer than your original code in regard of ordering of the rows: records are ordered in a subquery before the cumulative sum is computed.

Upvotes: 3

Related Questions