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?
Advertisement
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 |