How To Count Duplicate Or Repeating Records From Union All Query In SQL

Here's a query to count number of repeating records based from union all query. Union all query does not remove duplicate records. Here's a sample script:

  select resultsduplicate.url as job_item,  
  count(resultsduplicate.url) as repeat_occurence  
  from  
  (  
    SELECT url FROM mainTable  
     where id = 47 and url like '%testsite.com%' and _active = 1  
    UNION ALL  
    SELECT url FROM detailTable  
     where id = 47 and url like '%testsite.com%' and _active = 1  
  )resultsduplicate   
  group by resultsduplicate.url  
  having (count(resultsduplicate.url) >1);  

Original Source From (Stackoverflow.com)