MySQL/Wordpress: Select number of posts by month/year/day/etc, optionally filtered by category

gistfile1.sql

-- Select the count of posts grouped by year, month, day, hour, even minutes and seconds!
SELECT
  -- Number of posts on the specified time range
  COUNT(*) as "count",
	
  -- Time values to return
  -- These should also be listed under "group by" 
  -- If sorting is necessary, also include these in "order by"
  YEAR(p.post_date) as "year",
  MONTH(p.post_date) as "month",
  DAY(p.post_date) as "day",
  HOUR(p.post_date) as "hour"
	
-- You only "need" wp_posts, however if looking at a specific category, the other tables should be joined
FROM wp_posts p
  LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID
  LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
  LEFT JOIN wp_terms t ON t.term_id = tax.term_id

-- Only look for posts categorized under category ID's 5 or 9 (optional)
WHERE t.term_id IN (5, 9)

-- This affects the grouping of count. This should reflect the SELECT statement
GROUP BY 
  YEAR(p.post_date),
  MONTH(p.post_date),
  DAY(p.post_date),
  HOUR(p.post_date)
	
-- Sort the results so the first element is the most recent, and the last element is the oldest
-- To sort ASC, you should re-order these so the longest time increment comes last, instead of first
ORDER BY 
  YEAR(p.post_date) DESC,
  MONTH(p.post_date) DESC,
  DAY(p.post_date) DESC,
  HOUR(p.post_date) DESC

LIMIT 100;

/* Return Value:
count   year    month   day hour
1   2013    4   21  12
2   2013    3   3   12
1   2013    1   28  15
1   2013    1   20  11
1   2013    1   17  20
1   2012    12  9   10
1   2012    11  25  15
... */