Multisite posts in categories on network

Question

I'm running a multisite with a main site and 3 subsites (the number of subsites will be over 200 at the end). Now on every site I have a category called A, Category called B, and Category called C. On my main site I want to publish a count of all posts made on all subsites, all posts made on all subsites in category A, all posts made on all subsites in category B, all posts made on all subsites in category C. So I get the output like this:

• All posts: 100
• Posts in category A: 20
• Posts in category B: 30
• Posts in category C: 50

Of course a post can be in categories A abd B at the same time, and can only be counted one time.

I found the way to count all posts:

global $wpdb;
$post_count = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_2_posts WHERE post_status = 'publish' AND post_type = 'post' " );
$post_count1 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_3_posts WHERE post_status = 'publish' AND post_type = 'post' " );
$post_count2 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_4_posts WHERE post_status = 'publish' AND post_type = 'post' " );

$sum = $post_count + $post_count1 + $post_count2;

echo "<p>All posts {$sum}</p>";

I'm having trouble with the posts in certain categories. Here's what I have so far:

global $wpdb;
$wp_count_terms = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_2_term_taxonomy WHERE term_taxonomy_id = '22' " );
$wp_count_terms1 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_3_term_taxonomy WHERE term_taxonomy_id = '16' " );
$wp_count_terms2 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_4_term_taxonomy WHERE term_taxonomy_id = '13' " );

$sum = $wp_count_terms + $wp_count_terms1 + $$wp_count_terms2;

echo "<p>Posts in category A: {$sum}</p>";

But it's not working correctly. Am I on the right track at all?

Answer 1

Found the answer, posting the code below so it may help future generations.

<?php

global $wpdb;

$count_terms = $wpdb->get_var ("SELECT COUNT(*)
FROM wp_2_term_taxonomy, wp_2_posts, wp_2_term_relationships 
WHERE wp_2_posts.ID = wp_2_term_relationships.object_id 
AND wp_2_term_relationships.term_taxonomy_id = wp_2_term_taxonomy.term_taxonomy_id 
AND wp_2_term_taxonomy.term_id = '22' 
AND wp_2_posts.post_type = 'post' 
AND wp_2_posts.post_status = 'publish' ");

$count_terms1 = $wpdb->get_var ("SELECT COUNT(*)
FROM wp_3_term_taxonomy, wp_3_posts, wp_3_term_relationships 
WHERE wp_3_posts.ID = wp_3_term_relationships.object_id 
AND wp_3_term_relationships.term_taxonomy_id = wp_3_term_taxonomy.term_taxonomy_id 
AND wp_3_term_taxonomy.term_id = '16' 
AND wp_3_posts.post_type = 'post' 
AND wp_3_posts.post_status = 'publish' ");


$count_terms2 = $wpdb->get_var ("SELECT COUNT(*)
FROM wp_4_term_taxonomy, wp_4_posts, wp_4_term_relationships 
WHERE wp_4_posts.ID = wp_4_term_relationships.object_id 
AND wp_4_term_relationships.term_taxonomy_id = wp_4_term_taxonomy.term_taxonomy_id 
AND wp_4_term_taxonomy.term_id = '13' 
AND wp_4_posts.post_type = 'post' 
AND wp_4_posts.post_status = 'publish' ");



$sum = $count_terms + $count_terms1 + $count_terms2;
echo "<p>Category A sum: {$sum}</p>";

?>