Probably stating the obvious

jeremy.sql

select 
  ca1.source_id, 
  count(ca2.id) as 'total dupes',
  /* begin stuff you will replace with yucky dynamic SQL */
  sum(case ca2.source_id when 7785 then 1 else 0 end) as 'dupes_with_7785',
  sum(case ca2.source_id when 7795 then 1 else 0 end) as 'dupes_with_7795',
  sum(case ca2.source_id when 7805 then 1 else 0 end) as 'dupes_with_7805'
  /* etc */
  /* etc */
  /* etc */
  /* end stuff you will replace with dynamic SQL */  
from candidate_applications ca1
  inner join candidate_applications ca2 on 
     ca1.candidate_id = ca2.candidate_id 
     and ca1.source_id <> ca2.source_id
group by 
  ca1.source_id
order by ca1.source_id