# count your posts on ClassicPress (works for WordPress, but you'll probably need to change the table names)

# select all posts in your blog that are published or private, and that are of the type 'post'
SELECT YEAR(post_date) as year_val, COUNT(*) as total
FROM cp_posts
WHERE post_status IN("publish", "private") AND post_type = "post"
GROUP BY YEAR(post_date);

# select only posts in your blog that are published, and that are of the type 'post'
SELECT YEAR(post_date) as year_val, COUNT(*) as total
FROM cp_posts
WHERE post_status IN("publish") AND post_type = "post"
GROUP BY YEAR(post_date);

# select all comments in your database (which are not pingbacks, and which are approved)
SELECT YEAR(comment_date) as year_val, COUNT(*) as total
FROM cp_comments
WHERE comment_type = "comment" AND comment_approved = 1
GROUP BY YEAR(comment_date);

#select comments only for posts that have the stats "publish"
SELECT YEAR(c.comment_date) as year_val, COUNT(c.comment_date) as total
FROM cp_posts p, cp_comments c
WHERE p.post_status IN("publish") AND c.comment_type = "comment" AND c.comment_approved = 1
AND c.comment_post_ID = p.ID
GROUP BY YEAR(c.comment_date);

#count number of public posts for tags that appear more than once
SELECT t.name, COUNT(*) AS num
FROM cp_terms t, cp_term_taxonomy tx, cp_term_relationships rel, cp_posts p
WHERE t.term_id = tx.term_id
AND tx.term_taxonomy_id = rel.term_taxonomy_id
AND p.ID = rel.object_id
AND p.post_status = "publish"
AND tx.taxonomy = "post_tag"
GROUP BY t.name
HAVING COUNT(*) > 1;