im trying to get a count of all post for each Categorty and sub category

it should show

main_cat     10
  -a1       5
  -a2       5 

i have tried this but my counts are all of and im getting categories that are not listed

SELECT t.name as `name`, t1.name as parentName, count(p.ID) as `TotalPosts`,
    SUM(if(p.post_date>'2015-12-01 00:00:00',1,0)) as 'LastMonth' 
    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 AND tax.taxonomy ='category' 

    LEFT JOIN wp_terms t ON t.term_id = tax.term_id 

    LEFT JOIN wp_term_taxonomy tax1 ON tax.parent = tax1.term_taxonomy_id 

    LEFT JOIN wp_terms t1 ON t1.term_id = tax1.term_id 

    WHERE p.post_status = 'publish' 
    GROUP BY t.name
    ORDER BY t1.name,LastMonth DESC, TotalPosts Desc

im trying to recreate the page->category section the same way it is listed


1 Answer 1


You may try this amateur code.

FROM wp_posts  
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1  
AND ( 
  wp_term_relationships.term_taxonomy_id IN (1)
AND wp_posts.post_type = 'post' 
AND ((wp_posts.post_status = 'publish')) 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

Note the term_taxonomy_id IN (1) is where your category ID is 1. You may remove the line for published posts, so it will get any post.

You just need to run this code for any category you have.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.