I'm using mysql and trying to write a query that returns the month and year along with a count of the number of blog posts for that year. I am not
using WordPress, just for reference.
Here is my query so far which returns the month and year, but I need to get the number of blog posts for each month/year combination. I tried using the COUNT() function, but I am not getting the right counts. It seems to be counting each individual row, but I need to count the group
of rows for a given month/year.
SELECT DISTINCT CONCAT( MONTHNAME(date_created), ' ' , REPLACE(YEAR(date_created),',','')) AS post_month_year,
LOWER(MONTHNAME(date_created)) AS post_month,
LOWER(REPLACE(Year(date_created),',','')) AS post_year
The only columns needed should be post_id, date_created to do this it seems, I just can't get the query correct.