MySQL queries for article summaries part 1 of 2

Posted January 4th, 2009 in MySql

Yesterday I posted a summary of my posts for 2008 which counted the number of posts for 2008, number of days on which I posted, summary of posts by section. Today I've decided to show the MySQL queries behind the statistics. While your content management system / blogging system (or "off the shelf" one) will have different database structures you should be able to use similar queries to work similar numbers out for your own website.

Total articles posted

The simplest query of the lot, simply requires a sum of entries from the appropriate table selected by date range. The table my content is stored in is called "content" and the "date_created" field is the date and time the post lists as the post date.

To get a count of the posts for 2008, simply select a count from the table and set the date range to >= 2008-01-01 and < 2009-01-01. You could also do <= 2008-12-31 but because it's a datetime field you'd also need add 23:59:59 at the end to ensure you get everything from that day as well. Easier to simply to a < the next day. Nothing from that day will be included.

SELECT COUNT(*) 
FROM content
WHERE date_created >= '2008-01-01'
AND date_created < '2009-01-01'

Total articles excluding some

I also wanted to find out how many posts I'd made excluding the weekly and monthly roundups. This is easy in my system because there's a url field in the table and these ones all start with /weekly-roundup/ and /monthly-roundup/, so it's simply a matter of excluding those ones with "NOT LIKE" conditions. If you need to exclude them based on e.g. a tag then you'd need to join to another table and the query gets a little trickier.

SELECT COUNT(*) 
FROM content
WHERE date_created >= '2008-01-01'
AND date_created < '2009-01-01'
AND fullurl NOT LIKE '/monthly-roundup/%'
AND fullurl NOT LIKE '/weekly-roundup/%'

Total number of days articles were posted

The next statistic I wanted was the total number of days on which I made posts. Some days had more than one so the total number of days posted is less than the number of articles posted. Because the date posted is a datetime field I needed to use LEFT(date_created, 10) to get just the date portion of the field. Then do a COUNT(DISTINCT()) to get a count of the unique number of those to get the number of days posted on.

SELECT COUNT(DISTINCT(LEFT(date_created, 10)))
FROM content
WHERE date_created >= '2008-01-01'
AND date_created < '2009-01-01'

Summary by section & complete list of posts

The table structure in my database uses a tree format (it will change when I convert this blog to my new framework later this month or early next month) rather than a tag format, so the queries make a couple of joins. I'll detail those in a separate post early next week.