SQL Query to select Specific Month in MYSQL Database

Question

I have a database where I have date field 2020-08-03

I am trying to get the data for each month

SELECT COUNT(*) FROM new_caseWHERE sla_client = 'ITA' AND MONTH(sysdate) = MONTH(current_date ()) 
AND YEAR(sysdate) = YEAR(current_date())

The above works for the current month and year what would I use to query in the above for January 2020, February 2020 and so on

Not sure if https://stackoverflow.com/questions/937652/mysql-select-sum-group-by-date is similar. — Nigel Ren, Aug 04 '20 at 18:58
I am trying to echo just one month at a time, January's count = 20 from one Query as there is different queries with different months and functions — James Wright, Aug 04 '20 at 19:01

score 1 · Answer 1 · answered Aug 04 '20 at 18:44

I think that you want aggregation:

select
    date_format(mydate, '%Y-%m') yyyy_mm,
    count(*) cnt
from new_case
where sla_client = 'ITA'
group by date_format(mydate, '%Y-%m')
order by yyyy_mm

This gives you one row per month, with the count of rows in that month (if there is no data for a given month, then it just does not appear in the resultset).

If you need to filter on a given date range, then I would recommend using a where clause that filters directly on the date values rather than applying date functions. Say you want the last 3 months and the current month, then:

select
    date_format(mydate, '%Y-%m') yyyy_mm,
    count(*) cnt
from new_case
where 
    sla_client = 'ITA'
    and mydate >= date_format(current_date, '%Y-%m-01') - interval 3 month
    and mydate < date_format(current_date, '%Y-%m-01') + interval 1 month
group by date_format(mydate, '%Y-%m')
order by yyyy_mm

SQL Query to select Specific Month in MYSQL Database

1 Answers1