Nick asks how I got the stats I posted. Nothing special or fancy, I just ran a couple of simple SQL queries. I figured some of you might want to run them as well, so here they are:
Total # of posts in 2006:
SELECT COUNT(*)
FROM `wp_posts`
WHERE post_date >= '2006-01-01'
AND post_date < '2007-01-01'
AND post_status = 'publish'
Total # of comments in 2006:
SELECT COUNT(*)
FROM `wp_comments`
WHERE comment_date >= '2006-01-01'
AND comment_date < '2007-01-01'
AND comment_approved = '1'
Average length of posts in 2006:
SELECT AVG(LENGTH(post_content))
FROM `wp_posts`
WHERE post_date >= '2006-01-01'
AND post_date < '2007-01-01'
AND post_status = 'publish'
Total length of all posts in 2006:
SELECT SUM(LENGTH(post_content))
FROM `wp_posts`
WHERE post_date >= '2006-01-01'
AND post_date < '2007-01-01'
AND post_status = 'publish'
Note: These all assume the standard 'wp_' prefix for your database tables.
Thank you for putting this up. They are very handy and I have made a note of all of them and will use it for variety of tasks. 🙂
Thanks, man. I think a lot of folks will find value in that. 🙂
Alex nice stuff man thanks for posting
Noting you could skip the date range and just use:
WHERE post_date LIKE '2006%'
Very nice infos….thanks for that 🙂
KafkaesquÃ:
Yes, you are correct. You could use LIKE, but that is a free search and that usually eats up more CPU than a date range search (usually date is indexed, too).
Thanks for the nice tip, Alex.
[…] Alexking在Simple SQL Queries for Blog Statsä¸ç»™å‡ºäº†å…³äºŽwordpresså?šå®¢æ•°æ?®ç»Ÿè®¡çš„SQL查询命令。 […]
[…] Alexking在Simple SQL Queries for Blog Statsä¸ç»™å‡ºäº†å…³äºŽwordpressåšå®¢æ•°æ®ç»Ÿè®¡çš„SQL查询命令。 […]
[…] to Alex’s scripts, some details on my contributions to this space. This year saw 585 total posts, down slightly from […]
[…] stats about your blog which you can include in your year end review posts. Based on the queries by Alex King .rnWP Year End Stats is a simple WordPress Plugin which displays some fancy stats about your blog […]
[…] last time I ran these stats queries was in 2006; I totally forgot to do it for 2007. Fortunately, I remembered it today so I get to do […]
[…] Also for fun here are some yearly posting stats courtesy of Alex’s queries: […]
[…] little more than 3 years of personal blogging, throw some queries against the wall and see what […]
[…] some handy SQL queries, I’ve pulled together the following statistics from 2006 to the […]
[…] King (the genius behind Twitter Tools) uses some SQL queries to assess his blog posting each year. I used the queries to find out what had been happening here. […]
[…] l’ho rubata da Matt e ho preso spunto per le query da Alex King, un grazie a MrBrown per la dritta su una query che non andava e a Napolux per il link al codex […]
[…] solution: merge a number of different WordPress plugins into a single WordPress Mu plugin and then tie the results into a pair of easy to use flot […]
[…] The Article is https://alexking.org/blog/2007/01/01/sql-for-blog-stats. […]
[…] Blog an, die in einem Jahresabschluss Posting verwendet werden können. Das Plugin basiert auf MySQL Queries von Alex King. Verwandte […]
[…] As always, Alex King has the simple scripts. They still work – though do yourself a favor and use the "LIKE" method for […]
[…] me on Twitter may be aware, one of my downtime holiday projects was running Alex’s WordPress scripts against the database that runs this site. The results, while not surprising, were not […]
[…] Simple SQL Queries for Blog Stats | alexking.org (tags: database wordpress sql stats blog mysql blogs web) […]
[…] simple sql queries for blog stats – nick asks how i got the stats i posted. nothing special or fancy, i just ran a couple of simple sql queries. i figured some of you might want to run them as well, so here they are: total # of posts in 2006: select count(*) … […]
[…] yours using these SQL queries. […]
[…] yours using these SQL queries – maybe I should make this a plugin. […]