I am running WordPress and Let me write a custom query that will show a monthly archive of posts according to one specific category. I made use of the debug mode to discover tips to get a monthly archive according to all posts, choose a particular category. Listed here are the queries:

Get montlhy archive:

SELECT YEAR(post_date) AS `year`, MONTH (post_date) AS `month`, 
  count(ID) as posts FROM wp_posts  
  WHERE post_type = 'post' AND post_status = 'publish' 
  GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC

Choose category:

SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy
  AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy 
  IN ('category')  AND t.slug = 'blog' ORDER BY t.name ASC

I can not learn how to join both of these ) Any help appreciated!

Thanks.

Update:

Additionally, there are another table involved known as term_associations, to ensure that makes 4 tables as a whole. Here's a picture of WordPress' database structure: http://codex.wordpress.org/images/8/83/WP_27_dbsERD.png

Anyways, here's where I am at:

SELECT t.*, tt.*, YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, 
    count(ID) as posts FROM wp_posts, wp_term_relationships AS tr, 
    wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id 
    WHERE tt.taxonomy IN ('category') AND t.slug = 'blog' 
    AND post_type = 'post' AND post_status = 'publish' 
    AND ID = tr.object_id AND tr.term_taxonomy_id = t.term_id 
    GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC

I understand it isn't the easiest way around, which explains why I want your help )

Thanks.

What about something similar to this because the foundation for the joins and where clause additions that you'll require:

    SELECT 
        YEAR(post_date) AS `year`, 
        MONTH (post_date) AS `month`, 
        count(ID) as posts  
    FROM wp_posts 
    INNER JOIN wp_term_relationships wtr ON wp_posts.id = wtr.object_id
    INNER JOIN wp_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
    INNER JOIN wp_terms wt ON wtt.term_id = wt.term_id
    WHERE post_type = 'post' AND post_status = 'publish' 
    AND wtt.taxonomy = 'category'
    AND wt.name = 'enter category name here'
    GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC