CODEWITHSUNDEEP
mysqlsqldatecount
James Wright
James Wright

Reputation: 153

SQL Query to select Specific Month in MYSQL Database

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

Upvotes: 1

Views: 542

Answers (1)

GMB
GMB

Reputation: 222432

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

Upvotes: 1

Related Questions