Blog Posts and Word Count By Year

I wrote a SQL query to see how many blog posts I published by year – excluding the current year.

* Seven years ago, I removed around 750 blog posts that I thought sucked. Mostly from the 2005-2008 era.

Here is the SQL query, should you want to collect the same data for your WordPress blog.

SELECT YEAR(post_date), COUNT(*)
FROM wp_posts
WHERE post_status = 'publish'
ANDpost_type = 'post'
GROUP BY YEAR(post_date)
ORDER BY YEAR(post_date);

This is the SQL query for the total word count by year.

SELECT YEAR(post_date),
SUM(LENGTH('post_content') - LENGTH(REPLACE('post_content', ' ', ''))+1) WC
FROM wp_posts
WHERE post_status = 'publish'
AND post_type = 'post'
GROUP BY YEAR(post_date)
ORDER BY YEAR(post_date);

The chart images were made in Excel.

Let me know if you have any topic requests for 2022.