SELECT from the last 365 days (12 months) in sql

Question

Good morning! I’m having some difficulty making one SELECT selecting data from the last 365 days (12 months).

What I want you to show is: in the last 12 months you have to show (per month) a number (in this case the sum of id created that same month).

I think the mistake is in this part of the code:

BETWEEN now() AND DATE_ADD(now(), INTERVAL -12 MONTH)

But I’m not sure because Workbench no errors! Just shows nothing!

My code is this::

SELECT COUNT(id) AS id, DATE_FORMAT(create_time, '%m-%Y') AS data_mes
FROM ticket
WHERE ticket_state_id = 2 AND create_time BETWEEN now() AND DATE_ADD(now(), INTERVAL -12 MONTH)
GROUP BY MONTH(create_time);

Answer 1

Thank you very much to all those who are willing to help me, but fortunately I have already solved.

The code to solve is here:

SELECT COUNT(id) AS id, DATE_FORMAT(create_time, '%m-%Y') AS data_mes
FROM ticket
WHERE ticket_state_id = 2 AND create_time BETWEEN DATE_ADD(now(), INTERVAL -12 MONTH) AND now()
GROUP BY MONTH(create_time)
ORDER BY create_time;

The result is as follows:

Answer 2

good morning,

Validate if this is what you want

SELECT COUNT(id) AS id, DATE_FORMAT(create_time, '%m-%Y') AS data_mes
FROM ticket
WHERE ticket_state_id = 2 AND 
    DATEDIFF(day,create_time ,GETDATE())<=365
GROUP BY MONTH(create_time);