Yesterday I needed to get a count of the number of posts in a WordPress blog in each category. I used the following SQL code.
SELECT wpt.name, COUNT(wptr.object_id) as post_count FROM wp_terms wpt, wp_term_taxonomy wptt, wp_term_relationships wptr WHERE wpt.term_id = wptt.term_id AND wptt.taxonomy = 'category' AND wptt.term_taxonomy_id = wptr.term_taxonomy_id GROUP BY wpt.name
The same technique will work with WordPress MU based blogs as well. Just substitute the table names with those for the specific blog in the WordPress MU system.
Something to keep in mind is that the if you add up the total of the ‘post_count’ fields reported by this query it is likely to be more than the total number of posts in your blog. This is because posts can be in one or more categories.
