SOQL Query duplicate Clean Phone number

Question

I want to fetch duplicate records based on phone number. I have written soql query on devconsole like

List<Account>Ac=[Select Id, CSS_CustomerID__c, Customer_ID__c, 
    Customer_ID_ReadOnly__c,AFrontier_Prospect_Code__c,CSS_Prospect_Code__c,    
    IOG_Customer_ID__c, RecordType.Name, CleanPhone__c, CleanPhoneText__c, 
    CSS_ListingTel__c, Phone, CreatedDate  
 from Account group by CleanPhone__c having count(CleanPhone__c) > 1];

but its not working. Anyone have idea?

Answer 1

For finding a duplicate record, you need to group by the particular field you need to find duplicate on(CleanPhone__c) , in your case. Then use aggregate function count any other field

Use this query, in your case:

SELECT COUNT(Id), CleanPhone__c
FROM Account 
GROUP BY CleanPhone__c
HAVING COUNT(Id) > 1
ORDER BY COUNT(Id) DESC