WP > Term name & Count

WP > Term name & Count.php

<?php 
              global $wpdb;

              $terms = get_terms("categorias-y-tipos");
              $count = count($terms);
              $n = 0;
              if ( $count > 0 ){
                foreach ( $terms as $term ) {
                  $n++;
                  $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 = 'categorias-y-tipos'
                  AND $wpdb->term_taxonomy.term_id = '".$term->term_id."'
                  ";

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

                  echo "<li><a href='#tabs-". $n ."'>". $term->name . "<span>".  $term->count ."</span></a></li>";
                }
              }
              ?>