Simple SQL Queries for Blog Stats

Posted in: WordPress

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% [?]

Posted January 1st, 2007 @ 2:36 PM

23 Replies

  1. 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

  2. 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

  3. Shoemoney adds this Comment:

    Alex nice stuff man thanks for posting

    January 1st, 2007 at 4:42 pm

  4. 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

  5. pixelfactor.org adds this Comment:

    Very nice infos….thanks for that :)

    January 3rd, 2007 at 4:32 pm

  6. 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

  7. 查询wordpress?客资料的SQL命令 - ?客?盟 adds this Pingback:

    [...] Alexking在Simple SQL Queries for Blog Stats中给出了关于wordpress?客数?统计的SQL查询命令。 [...]

    May 1st, 2007 at 2:50 am

  8. 狐狸网事@Net » Blog Archive » 查询wordpress博客资料的SQL命令 adds this Pingback:

    [...] Alexking在Simple SQL Queries for Blog Stats中给出了关于wordpress博客数据统计的SQL查询命令。 [...]

    December 16th, 2007 at 12:28 pm

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

Add a Comment

Please note: Use of a non-personal web site or blog URL in the field below and/or comments that are off-topic, personal attacks, or support requests will likely be removed at my discretion. If you need help with WordPress or a WordPress plugin, please contact the WordPress HelpCenter.

Note: This post is over 3 years old. You may want to check later in this blog to see if there is new information relevant to your comment.

» «

About This Site

This is the personal web site of Alex King, a web developer in Denver, Colorado USA. More...


Crowd Favorite

Crowd Favorite is my software and web development business.

We build web applications, design and develop custom WordPress themes and plugins, and build custom sites using WordPress as a CMS.


I also have a tumblog that aggregates my online content from other services (Twitter, Flickr, del.icio.us. etc.).

America - america09.com

Ads