SQL Date range by month returns in multiple rows

I am looking to get the count of entries by the month. However, this is returning the total count.

select count(*) from TBL_NAME 
  where (dateAdded between '2020-01-01 00:00:00' and '2020-01-31 23:59:59') or
  (dateAdded between '2020-02-01 00:00:00' and '2020-02-29 23:59:59');

I would like it to be something like.

Month —– Count

January —- 500

February —- 600

If I can’t get the month in there, thats fine, but I would definitely like to have it so the count has its own row. How is something like that achieved?

Answer

That’s a straight aggregation query, where you want to count how many records exist for each month.

You can do:

select
    date_format(dateAdded, '%Y-%m-01') start_of_month,
    count(*) cnt
from tbl_name
where dateAdded >= '2020-01-01' and dateAdded < '2020-03-01'
group by start_of_month

The first column gives you the first day of the month for which data was aggregated – which seems more relevant that just the month name.