ID | SourceValue
-----------------
1  |  'value1'
2  |  'value1'
3  |  'value2'

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable1 dt1
  ON dvw.SourceValue = dt1.SourceValueColumn
  GROUP BY dvw.SourceValue

  UNION

  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable2 dt2
  ON dvw.SourceValue = dt2.SomeOtherColumn
  GROUP BY dvw.SourceValue
) AS q
GROUP BY q.SourceValue