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.
select count(`wp_posts`.`comment_count`) as postcount, sum(`wp_posts`.`comment_count`) as commentcount from (`wp_posts` join `wp_term_relationships`) where ((`wp_posts`.`ID` = `wp_term_relationships`.`object_id`) and (`wp_term_relationships`.`term_taxonomy_id` = 10))
This is our needed result and we have got it.