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

Question

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

Answer 1

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.

Answer 2

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 ...

Answer 3

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);