Today i had one task which required to find comment counts by category in WordPress. You know, wp_posts table doesn’t contain any information about post category. To collect full information about post and its category we need to get data from 3 tables. In this sample we will use just 2 tables, because we pass category id ourselves.
For example you enter to categories page from your dashboard and see that Category_A has 25 posts. But how many comments are there for these 25 posts? Let’s find it. Let’s think that Category_A’s ID is 10. (You can expressly get category_id from Dashboard->Categories page, if you look to category url with hovering cursor on Category name. There will be tag_ID=10 in the URL.)
The following query will show you Category_A’s posts count and comment count.
<br /> select count(`wp_posts`.`comment_count`) as postcount, sum(`wp_posts`.`comment_count`) as commentcount<br /> from (`wp_posts` join `wp_term_relationships`)<br /> where ((`wp_posts`.`ID` = `wp_term_relationships`.`object_id`) and<br /> (`wp_term_relationships`.`term_taxonomy_id` = 10))<br />
This is our needed result and we have got it.