Thanks to some leads from the wp-hackers list on this very question and some googling around, here is the answer to my problem
$sql = "SELECT count(*) as count,terms2.name as tag FROM wp_posts as p1
LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id,
wp_posts as p2
LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id =t2.term_taxonomy_id
LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
WHERE
t1.taxonomy = 'category' AND p1.post_status="publish" AND terms1.name="YOUR CATEGORY NAME HERE"
AND
t2.taxonomy = 'THAT OTHER CUSTOM TAXONOMY HERE - IN MY CASE THAT IS TO BE SOURCE' AND p2.post_status="publish"
AND p1.ID = p2.ID
GROUP BY tag
ORDER BY count DESC limit 5
";
global $wpdb;
$terms = $wpdb->get_results($sql,ARRAY_A);
foreach ($terms as $term):
extract($term);
echo "$tag,";
endforeach;
The above snippet will get me the top 5 source terms in the
desired category when I adjust the WHERE to be as
WHERE t1.taxonomy = 'category' AND p1.post_status="publish" AND
terms1.name="DESIRED CATEGORY NAME HERE" AND t2.taxonomy = 'Source'
AND p2.post_status="publish"
Not a SQL I recommend for using in high traffic sites but with some
transient caching, why not?