MySQL get sum of amount for last 6 months

Question

I'm trying to create a query to get the total amount for every month in the last 6 months. The idea is something like:

Select 
 sum(amount) as Total, data
From 
 myTable
Where
 date /* How can I filer by month? */
Group By
 /* Group by each month */

Sample data

create table mytable ( Order_date date, Amount float);

insert into mytable values('2021-02-13',24.15);  
insert into mytable values('2021-02-13',12.00  );
insert into mytable values('2021-02-16',14.12  );
insert into mytable values('2021-03-02',17.01  );
insert into mytable values('2021-03-14',18.25  );
insert into mytable values('2021-4-1',19.24  );

Amount is decimal and date is date time field.

Desired output:

Total	month
50.269999504089355	2021 February
17.010000228881836	2021 March

Is it possible to create this with only one query?

please show us your schema for `myTable`. We don't know what your columns are, so filtering the date could be done a dozen different ways. — WOUNDEDStevenJones, Mar 09 '21 at 19:32
To filter out last 6 month : https://stackoverflow.com/questions/14643454/get-the-values-for-last-6-months-in-mysql/14643487 Group by month : https://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year — Raseena Abdul, Mar 09 '21 at 19:34

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-10T13:01:38.350

1

Of course you can. Try this query:

Select 
    sum(amount) as Total,  EXTRACT(MONTH FROM date)
From 
    myTable
Where
    date>= DATE_FORMAT(NOW() ,'%Y-%m-01')  - INTERVAL 6 MONTH
Group By
    EXTRACT(MONTH FROM date)

But only month can be duplicate if you consider date range for more than year. january 2020 and january 2021 will be same in above query. So you should group by on month and year as below:

Schema (MySQL v5.7)

create table mytable ( Order_date date, Amount float);

insert into mytable values('2021-02-13',24.15);  
insert into mytable values('2021-02-13',12.00  );
insert into mytable values('2021-02-16',14.12  );
insert into mytable values('2021-03-02',17.01  );
insert into mytable values('2021-03-14',18.25  );
insert into mytable values('2020-12-1',19.24  );

Query #1

Select 
    sum(amount) as Total, 
    concat(year(order_date), ' ' , monthname(Order_date)) month
From 
    mytable
Where
    Order_date between CURDATE() - INTERVAL 6 MONTH and curdate()
Group By
    concat(year(order_date), ' ' ,monthname( Order_date) );

Output:

Total	month
19.239999771118164	2020 December
50.269999504089355	2021 February
35.260000228881836	2021 March

View on DB Fiddle

edited Mar 10 '21 at 13:01

answered Mar 09 '21 at 19:35

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

Thank you. I'm not sure why the post was closed. I think I cleared explained wha ti needed. The filter for last 6 months is not working. For example, insert in the table some data for September 2020 or after March 202. These data should not show in the query, but it does – myTest532 myTest532 Mar 09 '21 at 19:56
You explained clearly. Let me check the condition. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 20:00
i have just checked second query and it’s not adding any value from march 2020. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 20:04
but It's not workinf for future date. changed the answer – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 20:06
it will now ignore future dates. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 20:07
You might edit the question with sample input output to reopen the question if you want. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 20:08
Thank you. It works like a champ – myTest532 myTest532 Mar 09 '21 at 20:25
You are most welcome. Best wishes. – Kazi Mohammad Ali Nur Romel Mar 09 '21 at 20:26
Just one minor details in my case, I wanted the last 6 months including the current month, so I change the where condition to ```INTERVAL 5 MONTH```. My bad, I should have explained it – myTest532 myTest532 Mar 09 '21 at 20:41
Another detail, the filter is not actually getting the first day of the 6 months ago. It's filtering by ```curdate()```, so it is considering the current day, not the first day of the month. – myTest532 myTest532 Mar 09 '21 at 22:35
I have changed my answer – Kazi Mohammad Ali Nur Romel Mar 10 '21 at 13:02

MySQL get sum of amount for last 6 months

1 Answers1