Ty jo, ty tu strukturu máš snad ještě horší, než jsem si myslel
SELECT Contentgroup, count(distinct Album, Contentgroup) as pocet_alb
FROM
(
SELECT Contentgroup, Album FROM `zahranicni`
UNION ALL
SELECT Contentgroup, Album FROM `domaci`
)
group by Contentgroup