1

How can I make a cumulative sum of mysql column and group by month year here is my table "saving"

id date         value
1  01/01/2018   10
2  01/12/2018   5
3  02/03/2018   6
4  02/23/2018   4

I try this query

SET @SumVariable:=0;
select CONCAT(LEFT(monthname(date),3),'-', year(date)) as month
    ,sum(value)
    ,(@SumVariable := @SumVariable + sum(value)) AS total
FROM saving
GROUP BY year(date), month(date) LIMIT 50

The query returns 

month      value
jan-2018    15
feb-2018    10

It's not cumulative the sum, I need the output to be as follow

month       total
jan-2018    15
feb-2018    25
ekad
  • 14,436
  • 26
  • 44
  • 46
user1991131
  • 37
  • 1
  • 5
  • 1
    See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Mar 07 '18 at 15:43
  • 1
    You should not be saving date data as d/m/Y strings. You should make the column date-type so that you can simply use MySQL's native functions in the GROUP BY. – mickmackusa Aug 17 '22 at 22:55
  • Does this answer your question? [mysql select sum group by date](https://stackoverflow.com/questions/937652/mysql-select-sum-group-by-date) – mickmackusa Aug 17 '22 at 22:56

1 Answers1

1

Try this :

select date_format(s1.date,'%b-%Y') as month,
       (select sum(s2.value) 
        from saving s2 
        where s2.date <= last_day(s1.date)) as total
from saving s1
group by month
order by s1.date
limit 50;

You can see more information on this question :

Create a Cumulative Sum Column in MySQL

A.Baudouin
  • 2,855
  • 3
  • 24
  • 28