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: 4% [?]
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
WP-YearEndStats adds this Pingback:
[...] Blog an, die in einem Jahresabschluss Posting verwendet werden können. Das Plugin basiert auf MySQL Queries von Alex King. Verwandte [...]
July 12th, 2009 at 12:38 am
2008 and 2009 Blog Stats | NSLog(); adds this Pingback:
[...] As always, Alex King has the simple scripts. They still work – though do yourself a favor and use the "LIKE" method for [...]
January 2nd, 2010 at 2:33 pm
tecosystems » The Length in 2010 adds this Pingback:
[...] 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 [...]
January 4th, 2010 at 10:17 am
plainwrek / links for 2010-01-08 adds this Pingback:
[...] Simple SQL Queries for Blog Stats | alexking.org (tags: database wordpress sql stats blog mysql blogs web) [...]
January 8th, 2010 at 6:06 am
Orlando Advertising Agency Articles » Blog Archive » Google PageRank Tips adds this Pingback:
[...] 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(*) … [...]
January 16th, 2010 at 6:44 am