MySQL get sum of amount for last 6 months [closed]

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?

Answer

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