Is it possible to retrieve list of Accounts which has more than 100 contacts associated to it using a single SOQL ?

  • 5
    can you please share what attempt / options you tried so far and highlight where you got issues with your current attempt ? Sep 2 '16 at 1:53

Solution 1 You can do this in single soql via :

Select AccountId from Contact group by AccountId having count(Name) > 100

Solution 2 You have to create a roll-up summary field on account object to count related contacts. Then by this field you can fetch accounts who has more than 100 contacts.

  • Unfortunately, it isn't possible to make a rollup summary field on Account that summarizes Contact. However, this could be accomplished with a trigger, or by using Andrew Fawcett's declarative lookup rollup summary tool. The query is probably the better way to go if this information is only needed in one place.
    – Derek F
    Sep 2 '16 at 3:03

I think I misinterpreted your initial question. You can filter on aggregations using the HAVING clause:

SELECT AccountId, count(Id) records FROM Contact GROUP BY Accountid HAVING count(Id) > 100

You should be able to retrieve at least 999 contacts using:

SELECT Name, (SELECT Id FROM Contacts) FROM Account WHERE ...

You can use simple query like--- SELECT Id, Name, ( SELECT Name, Email, BirthDate FROM Contacts ) FROM Account where Name='Anshul Arora'

Or you can use LIST in your class to achieve dynamic things Exp----

List acc= [SELECT Id, Name FROM Account];

System.debug('a' + alist);

List clist = [SELECT Id, Name FROM Contact WHERE Contact.AccountId IN :acc];

System.debug('c' + acc);

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.