Calculate a Running Total in MySQL

Calculate a running total in MySQL

Perhaps a simpler solution for you and prevents the database having to do a ton of queries. This executes just one query then does a little math on the results in a single pass.

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(`date`) AS d,
       COUNT(*) AS c
    FROM  `orders`
    WHERE  `hasPaid` > 0
    GROUP  BY d
    ORDER  BY d) AS q1

This will give you an additional RT (running total) column. Don't miss the SET statement at the top to initialize the running total variable first or you will just get a column of NULL values.

Running total with in each group using MySQL

In MySQL, the most efficient approach is to use variables:

select e.*,
       (@s := if(@id = e.id, @s + salary,
                 if(@id := e.id, salary, salary)
                )
       ) as running_salary
from (select e.*
      from employee e
      order by e.id, e.month
     ) e cross join
     (select @id := -1, @s := 0) params;

You can also do this with a correlated subquery:

select e.*,
       (select sum(e2.salary)
        from employee e2
        where e2.id = e.id and e2.month <= e.month
       ) as running_salary
from employee e;

How to calculate running sum for each group in MySQL

First calculate the running sum of the scores for each month in a CTE.

Then apply your conditions:

with cte as (
  select date_format(dateOfExam, '%Y-%m') ExamMonth,
         dateOfExam, score, 
         sum(score) over (partition by date_format(dateOfExam, '%Y-%m') order by dateOfExam) total
  from student
)
select ExamMonth, dateOfExam, score, 
       case when sum(total >= 10) over (partition by ExamMonth order by dateOfExam) = 1 then 'Y' end Reward1,
       case when sum(total >= 20) over (partition by ExamMonth order by dateOfExam) = 1 then 'Y' end Reward2
from cte

See the demo.

Results:

> ExamMonth | dateOfExam | score | Reward1 | Reward2
> :-------- | :--------- | ----: | :------ | :------
> 2020-05   | 2020-05-28 |     5 | null    | null   
> 2020-05   | 2020-05-29 |     5 | Y       | null   
> 2020-05   | 2020-05-30 |    10 | null    | Y      
> 2020-06   | 2020-06-03 |    10 | Y       | null   
> 2020-06   | 2020-06-05 |     5 | null    | null   
> 2020-07   | 2020-07-21 |    20 | Y       | Y      
> 2020-07   | 2020-07-22 |    10 | null    | null   
> 2020-07   | 2020-07-28 |    10 | null    | null 

How to calculate reverse running sum in MySQL

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.

---

Related Topics