Wednesday, December 5, 2012

Count duplicate or repeating records from union all query

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:
1:  select resultsduplicate.url as job_item,  
2:  count(resultsduplicate.url) as repeat_occurence  
3:  from  
4:  (  
5:    SELECT url FROM mainTable  
6:     where id = 47 and url like '%testsite.com%' and _active = 1  
7:    UNION ALL  
8:    SELECT url FROM detailTable  
9:     where id = 47 and url like '%testsite.com%' and _active = 1  
10:  )resultsduplicate   
11:  group by resultsduplicate.url  
12:  having (count(resultsduplicate.url) >1);  
Original Source (Stackoverflow.com) Cheers!

0 comments:

Post a Comment