3

While running the following query it returns only one duplicate account name but not all the accounts that has the same name.How to get other matching duplicate names?

acc = [SELECT Name,count(id),AccountNumber FROM Account GROUP BY Name,AccountNumber HAVING count(Name)>1];

Result while adding inner loops:

   Set<String> duplicateNames = new Set<String>();
Public DuplicateAccNames()
{
acc = [SELECT Name,count(id) FROM  Account group by Name having  count(Name)>1];
System.debug('acc count'+acc);
//acc = [select  id,Name,AccountNumber, Phone from Account group by id,name,AccountNumber,Phone having count(Name)>1 ];
for(AggregateResult ac1 : acc)
{
duplicateNames.add((String)ac1.get('Name'));
List<Account> duplicates = [SELECT Name, AccountNumber
    FROM Account
    WHERE Name IN :duplicateNames];
 System.debug('test'+duplicates[1].Name);
}
}

enter image description here

4

Right now, you're getting a List<AggregateResult>. You need a List<Account> if you want to see the individual records. Probably the simplest way to take what you have already and get what you want is to collect a Set<String> duplicateNames then add another query.

Set<String> duplicateNames = new Set<String>();
for (AggregateResult aggregate : [/*existing query*/])
{
    duplicateNames.add((String)aggregate.get('Name'));
}
for (Account account : [
    SELECT Name, AccountNumber
    FROM Account
    WHERE Name IN :duplicateNames
]){
    system.debug(account.AccountNumber);
}
  • 2
    And we have to do this because we can't do: select id, name from account where name in (select name from account group by name having count(id) > 1). Silly SOQL. – sfdcfox Aug 16 '16 at 17:33
  • @sfdcfox Yeah added flexibility on inner-joins would be slick. But it would probably still have to be on indexed fields for performance reasons. I wonder if there's an Idea out there for that. – Adrian Larson Aug 16 '16 at 17:34
  • Nope@adrian and sfdcfox,.Its still returning only the count expr0 as 2 but not both the names – Priyalakshmi Aug 16 '16 at 17:55
  • You need to look at the List<Account>, which doesn't use count or GROUP BY or HAVING. You won't get individual records when you use aggregation. – Adrian Larson Aug 16 '16 at 17:57
  • Yeah but its returning multiple account Names instead of 4.I have update my Question – Priyalakshmi Aug 16 '16 at 18:09
0

Does this work for you ?

SELECT Name,count(id),AccountNumber FROM Account GROUP BY CUBE( Name,AccountNumber) HAVING count(Name)>1

List aggregate = [SELECT Name,count(id),AccountNumber FROM Account GROUP BY CUBE( Name,AccountNumber) HAVING count( Name)>1 ]; system.debug ((string)aggregate[0].get('AccountNumber'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.