Select the previous 3 months

Question

Select the previous 3 months

Navigation

#1 by (1 votes)

0

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

That 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

mysql

asked by anonymous 03.03.2017 / 20:31

1 answer

Mail () - attached file in white php Select with max (date), penultima (date)

score 1 · Accepted Answer

You can use GROUP BY to group by MONTH() and YEAR() and also use WHERE to only get whichever is greater 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 because WHERE will only apply for dates that are greater than the first day of three months ago.

YEAR(data), MONTH(data) DESC groups everything that is from the same year and the same month. Your query has a problem with MONTH(data) = MONTH(NOW()) , because it does not consider that 2016-12-01 and 2017-12-01 has the same month ( 12 ), but different years.
li>
NOW() - INTERVAL 3 MONTH get the current date and return three months, so now ( 2017-03-03 16:54:53 ) becomes 2016-12-03 16:54:53 .
DATE_FORMAT(..., '%Y-%m-01') causes 2016-12-03 16:54:53 to become 2016-12-01 , that is, it will always be the first day of the month.

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