Count posts for month and year

2015-09-08

Here is a small SQL command that counts the number of posts in your database table and group the count by both the month and the year they were published. This command is made to use the standard WordPress posts table but can easily be modified for any database table.

SELECT 
    DATE_FORMAT(post_date, '%Y') AS year, 
    DATE_FORMAT(post_date, '%m') AS month, 
    COUNT(*) AS count 
FROM wp_posts 
GROUP BY year, month;

It will give you a table like this, which you might recognize as a popular widget on many WordPress sites.

year month count
2015 07 21
2015 08 3
2015 09 2