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.
Popularity: 6% [?]
Raj adds this Comment:
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.
January 1st, 2007 at 3:37 pm
Geof F. Morris adds this Comment:
Thanks, man. I think a lot of folks will find value in that.
January 1st, 2007 at 4:27 pm
Shoemoney adds this Comment:
Alex nice stuff man thanks for posting
January 1st, 2007 at 4:42 pm
Kafkaesquí adds this Comment:
Noting you could skip the date range and just use:
WHERE post_date LIKE '2006%'January 2nd, 2007 at 11:17 pm
pixelfactor.org adds this Comment:
Very nice infos….thanks for that
January 3rd, 2007 at 4:32 pm
Peter adds this Comment:
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.
January 27th, 2007 at 7:44 am
查询wordpress?客资料的SQL命令 - ?客?盟 adds this Pingback:
[...] Alexking在Simple SQL Queries for Blog Stats中给出了关于wordpress?客数?统计的SQL查询命令。 [...]
May 1st, 2007 at 2:50 am
狐狸网事@Net » Blog Archive » 查询wordpress博客资料的SQL命令 adds this Pingback:
[...] Alexking在Simple SQL Queries for Blog Stats中给出了关于wordpress博客数据统计的SQL查询命令。 [...]
December 16th, 2007 at 12:28 pm
tecosystems » 2007: The Year in Review, from Macro to Micro adds this Pingback:
[...] to Alex’s scripts, some details on my contributions to this space. This year saw 585 total posts, down slightly from [...]
January 2nd, 2008 at 1:30 am
WordPress Plugins Database » Plugin Details » Year End Stats adds this Pingback:
[...] 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 [...]
August 14th, 2008 at 5:32 am
Five on Friday: 2008 Stats – rebelpixel.com adds this Pingback:
[...] 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 [...]
January 1st, 2009 at 10:55 am
Top Emailers 2008, etc — Matt Mullenweg adds this Pingback:
[...] Also for fun here are some yearly posting stats courtesy of Alex’s queries: [...]
January 3rd, 2009 at 10:27 am
My Waisted Life » Blog Archive » Growing Up, Finally adds this Pingback:
[...] little more than 3 years of personal blogging, throw some queries against the wall and see what [...]
January 3rd, 2009 at 9:07 pm
Sleeping Monkey Speaks - » Sleeping Monkey Statistics adds this Pingback:
[...] some handy SQL queries, I’ve pulled together the following statistics from 2006 to the [...]
January 5th, 2009 at 2:51 am
Blog entry statistics « b&k m online adds this Pingback:
[...] 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. [...]
January 6th, 2009 at 4:33 pm
Statistiche del Blog « WordPress Italy Planet adds this Pingback:
[...] 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 [...]
January 9th, 2009 at 3:53 am
Combined Blog Stats from Google Analytics, Google Reader, Feedburner, and WordPress | Wulf adds this Pingback:
[...] 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 [...]
January 17th, 2009 at 8:11 pm
Sheldon Lendrum » Archive » WordPress Post Statistic Treats adds this Pingback:
[...] The Article is http://alexking.org/[...]r-blog-stats. [...]
February 6th, 2009 at 2:15 pm