MySQL sum amount by each months from last 12 month to this month

Question

I want to sum amount of each month from last 12 months ago until now. So how to write the MySQL query, I'm so poor with MySQL.

select sum(amount) as monthly_expense from tb_expense 
where between last 12 and now();

Please, read [how to ask useful questions aka MCVE](https://stackoverflow.com/help/mcve). — LMC, Apr 02 '18 at 01:17
[mysql select sum group by date](https://stackoverflow.com/q/937652/2943403) — mickmackusa, Aug 17 '22 at 22:58

score 0 · Answer 1 · answered Apr 02 '18 at 01:34

You have not mentioned your table format and which field stores date on your table. But for your functionality you need to have a column which stores date for each record. Assuming you have a column expense_date try this :

SELECT SUM(amount) AS monthly_expense FROM tb_expense 
WHERE tb_expense.date > (DATE_SUB(CURDATE(), INTERVAL 12 MONTH))

Let us know if your table data is different.

score 0 · Answer 2 · answered Apr 02 '18 at 01:35

You basically want:

select sum(amount) as monthly_expense
from tb_expense
where datecol >= curdate() - interval 1 year;

datecol is whatever column stores the relevant date in the table.

If you want this per month, then use group by:

select year(datecol), month(datecol), sum(amount) as monthly_expense
from tb_expense
where datecol >= curdate() - interval 1 year
group by year(datecol), month(datecol);

Note that in this formulation, the first and last months will be partial. That is what your question specifically asks for.

MySQL sum amount by each months from last 12 month to this month

2 Answers2

Linked