Select the previous 3 months

Question

Hello, I have the following table:

----------------------
downloads | data
----------------------
10        | 2016-12-01
15        | 2016-12-02
20        | 2017-01-01
30        | 2017-02-01
40        | 2017-03-01

This way I can add the number of downloads for the current month:

SELECT downloads, data,
SUM(downloads) AS soma
FROM tabela
WHERE MONTH(data) = MONTH(NOW());

My question is, I would like to select the current month and the previous 3:

12/2016: 25 Downloads
01/2017: 20 Downloads
02/2017: 30 Downloads
03/2017: 40 Downloads

Answer 1

You can use the GROUP BY to group by MONTH() and YEAR() and also use the WHERE to only get what’s bigger than the last three months.

SELECT   downloads, 
         data, 
         SUM(downloads) AS soma
FROM     tabela 
GROUP BY YEAR(data), 
         MONTH(data) DESC 
WHERE    data >= ( DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m-01') )

This will cause you to group by YEAR(data), MONTH(data) DESC and due to WHERE will only apply for dates that are longer than the first day of three months ago.

---

• YEAR(data), MONTH(data) DESC groups together everything from the same year and the same month. Your query has a problem in MONTH(data) = MONTH(NOW()), because it does not consider that so much 2016-12-01 how much 2017-12-01 has the same month (12), however different years.

• NOW() - INTERVAL 3 MONTH obtain the current date and turning back three months, just now (2017-03-03 16:54:53) becomes 2016-12-03 16:54:53.

• DATE_FORMAT(..., '%Y-%m-01') causes the 2016-12-03 16:54:53 becoming 2016-12-01, that is it will always be the first day of the month.

If you’re using DATETIME, change to %Y-%m-01 00:00:00!