List the number of posts for each custom taxonomy and specific custom field value

Question

I would like to list my posts by a specific taxonomy. At the same time, for each taxonomy, I woud like to count the number of posts with a specific custom field value. So the list would look like this: for each: custom taxonomy name / count of posts / count of posts in this taxonomy that contains custom post field (key) with specific value

All I have been able to do so far is to list custom taxonomy and the count of posts (simply by listing category or by wp database query) or to list posts with specific meta_value for meta_key. I couldn't combine both though:

for the custom taxonomy list and post count:

 $variable = wp_list_categories('taxonomy=organy&echo=0&show_count=1&title_li=&');
$variable = preg_replace('~\((\d+)\)(?=\s*+<)~', '<span class="rank-nbr">$1</span>', $variable);
echo $variable;

for the custom field's value post count:

$status_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value = 'fixed';");
echo '<p>number of posts: ' . $status_count . '</p>';

I figure it should be done by wpdb query.

I would appreciate help from more advanced programmers...

My another attempt is:

$terms = get_terms("MY_TAXONOMY_TERM");
$count = count($terms);
$meta_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value = 'MY_SPECIFIC_VALUE';" );

if ( $count > 0 ){
echo "<tr>";
foreach ( $terms as $term ) {
echo "<td>" . $term->name  ,"</td><td> ",  $term->count . "</td><td> ",  $meta_count, "<td>";

}
echo "</tr>";
}

But here what happens is the list of all the posts with a specific value (MY_SPECIFIC_VALUE).

Answer 1

The solution is:

global $wpdb;

$terms = get_terms("TAXONOMY_TERM");
$count = count($terms);

if ( $count > 0 ){
foreach ( $terms as $term ) {

$querystr = "

SELECT count(ID) as count FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE exists ( SELECT * FROM $wpdb->postmeta WHERE ($wpdb->postmeta.post_id = $wpdb->posts.ID) AND meta_key = 'META_KEY' and meta_value = 'META_VALUE' )
AND (post_status = 'publish' )
AND $wpdb->term_taxonomy.taxonomy = 'TAXONOMY_TERM'
AND $wpdb->term_taxonomy.term_id = '".$term->term_id."'
";

$res= $wpdb->get_results($querystr);

echo "<tr><td>" . $term->name  ,"</td><td> ",  $term->count , "</td><td> ",  $res[0]->count, "</td></tr>";
}
}