I’m sure that a custom sql query would work much better but here is an option using the WordPress Tools available
//first get all categories
$categories = get_terms( 'category', array(
'orderby' => 'count',
));
//then create an array for easier processing
foreach ( $categories as $cat ) {
$slugs[] = $cat->slug;
$counts[$cat->slug]['count'] = $cat->count;
}
//then loop over the categories and for each one create a "query" to count the number of available products
foreach($slugs as $term){
$products = get_posts(array(
'post_type' => 'product',
'posts_per_page' => -1,
'tax_query' => array(
'relation' => 'AND',
array(
'taxonomy' => 'category',
'field' => 'slug',
'terms' => array( $term)
),
array(
'taxonomy' => 'product_status',
'field' => 'slug',
'terms' => array( "available"),
'operator' => 'NOT IN',
)
)
));
$counts[$term]['available'] = count($products);
}
//then all the is left is to print everyting Out
if (count($counts) > 0){
echo '<table><tr><td>Category</td><td>No. of products</td><td>Products available</td></tr>';
foreach ($counts as $key => $val){
echo '<tr><td>'.$key.'</td><td>'.$var['count'].'</td><td>'.$var['available'].'</td></tr>';
}
echo '</table>';
}