Get terms of posts with category with sql

Here’s the solution:

global $wpdb;
$wpdb->get_results( $wpdb->prepare(
    "SELECT tags.*, COUNT(tags_rel.object_id) as posts_count
    FROM
        {$wpdb->prefix}terms tags
        INNER JOIN {$wpdb->prefix}term_taxonomy tags_tax ON (tags_tax.term_id = tags.term_id)
        INNER JOIN {$wpdb->prefix}term_relationships tags_rel ON (tags_tax.term_taxonomy_id = tags_rel.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}posts posts ON (tags_rel.object_id = posts.ID)
        INNER JOIN {$wpdb->prefix}term_relationships cats_rel ON (posts.ID = cats_rel.object_id)
        INNER JOIN {$wpdb->prefix}term_taxonomy cats_tax ON (cats_rel.term_taxonomy_id = cats_tax.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}terms cats ON (cats.term_id = cats_tax.term_id)
    WHERE
        tags_tax.taxonomy = 'type'
        AND cats_tax.taxonomy = 'category'
        AND posts.post_type="post"
        AND posts.post_status="publish"
        AND cats.term_id = %d
    GROUP BY tags_tax.term_id",
    <CATEGORY_TERM_ID>  // <-- here goes the category id of current category
) );

PS. Remember that this is pretty nice opportunity to do some caching. These tags won’t change themselves. So you can compute them once and them use those cached values. This way they will be computed only during saving of posts…