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.

2 Comments

Add yours

  1. Thank you for such a great blog. (I’ve been reading your blog now for 2-3 years I think?) Your posts and experiences always give me so much to think about and use to evaluate my own experiences and experiments.

    I would love to see another update about where you are with your health, diet, and exercise in 2022.

  2. @Keturah – will do. Thanks for the comment.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.