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

Posted January 1st, 2007 @ 2:36 PM

18 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

Add a Comment

Please note: Use of a non-personal web site or blog in the field below and/or comments that are off-topic, personal attacks, or support requests will likely be removed at my discretion.

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

Blockbuster vs. Netflix » « 2007 Goals

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

web design services