Having difficulty with a SUMIFS scenario on MySQL
Here's my data:
CREATE TABLE opportunities
(close_date DATE NOT NULL,
sale_amount DECIMAL NOT NULL);
INSERT INTO opportunities VALUES ('20190101',100);
INSERT INTO opportunities VALUES ('20190115',115);
INSERT INTO opportunities VALUES ('20190210',150);
INSERT INTO opportunities VALUES ('20190212',180);
INSERT INTO opportunities VALUES ('20190306',200);
INSERT INTO opportunities VALUES ('20190315',220);
INSERT INTO opportunities VALUES ('20190422',250);
INSERT INTO opportunities VALUES ('20190428',260);
INSERT INTO opportunities VALUES ('20190509',300);
INSERT INTO opportunities VALUES ('20190521',310);
INSERT INTO opportunities VALUES ('20190601',325);
INSERT INTO opportunities VALUES ('20190630',375);
INSERT INTO opportunities VALUES ('20190703',400);
INSERT INTO opportunities VALUES ('20190716',500);
INSERT INTO opportunities VALUES ('20190813',550);
INSERT INTO opportunities VALUES ('20190814',575);
INSERT INTO opportunities VALUES ('20190909',625);
INSERT INTO opportunities VALUES ('20190929',650);
INSERT INTO opportunities VALUES ('20191004',700);
INSERT INTO opportunities VALUES ('20191022',750);
INSERT INTO opportunities VALUES ('20191112',800);
INSERT INTO opportunities VALUES ('20191119',900);
INSERT INTO opportunities VALUES ('20191202',950);
INSERT INTO opportunities VALUES ('20191216',975);
What I need to accomplish is to have 1 row per Month-Year (e.g. 2019-01, 2019-02, 2019-03, etc.) and for that row, I need to do a sum of the sale_amount for the previous 6 months. So 2019-12 will have a sale_amount equal to the sum of all sale_amounts with a close_date of July 1st 2019 thru Dec 31 2019. Based, on the data that I have provided, I need my final results to look like this:
Month Sale_Amount (Prev 6 Months)
2019-01 215
2019-02 545
2019-03 965
2019-04 1475
2019-05 2085
2019-06 2785
2019-07 3470
2019-08 4265
2019-09 5120
2019-10 6060
2019-11 7150
2019-12 8375
Here's what I tried so far:
SELECT
DATE_FORMAT(op.close_date, '%Y-%m'),
SUM(CASE
WHEN op.close_date >= DATE_FORMAT(DATE_SUB(op.close_date, INTERVAL 5 MONTH) ,'%Y-%m-01')
AND op.close_date <= LAST_DAY(op.close_date) THEN op.sale_amount
END)
FROM opportunities op
GROUP BY DATE_FORMAT(op.close_date, '%Y-%m')
ORDER BY DATE_FORMAT(op.close_date, '%Y-%m')
This doesn't work, it only returns the sum for that month
Actual results I'm getting:
Month Sale_Amount (Prev 6 Months)
2019-01 215
2019-02 330
2019-03 420
2019-04 510
2019-05 610
2019-06 700
2019-07 900
2019-08 1125
2019-09 1275
2019-10 1450
2019-11 1700
2019-12 1925
I'm trying to convert this from an Excel spreadsheet, The formula below works exactly as I intend, but not sure how to translate to MySQL
SUMIFS($B$2:$B$500, --sum range
$A$2:$A$500,">="&DATE(YEAR(EOMONTH(A2,-6)+1),MONTH(EOMONTH(A2,-6)+1),1), --close_date >= than first day 6 months ago
$A$2:$A$500,"<="&DATE(YEAR(A2),MONTH(A2),DAY(EOMONTH(A2,0)))) --close date <= last date of current month
Hope my question is clear enough, thanks in advance
Here is a solution for MySQL < 8.0:
select d.close_month, sum(o.sale_amount) sale_amount
from
(select distinct date_format(close_date, '%Y-%m-01') close_month from opportunities) d
inner join opportunities o
on o.close_date < close_month + interval 1 month
and o.close_date >= close_month - interval 5 month
group by d.close_month
order by d.close_month
This works by generating a list of distinct months, joining it with the table on the 6 last months, and then aggregating.
close_month | sale_amount :---------- | ----------: 2019-01-01 | 215 2019-02-01 | 545 2019-03-01 | 965 2019-04-01 | 1475 2019-05-01 | 2085 2019-06-01 | 2785 2019-07-01 | 3470 2019-08-01 | 4265 2019-09-01 | 5120 2019-10-01 | 6060 2019-11-01 | 7150 2019-12-01 | 8375