[SalesForce] How to query Account object with minimum number of children

There is a scenario where I need to query the Account records that has the associated contacts >= 100, Since accounts and contact are not Master Detail relation ship I could not create roll-up summary.

I tried a similar query from stack exchange from the previous Question however that did not worked.

Select id,(Select Id from Contacts)From Account Where Id IN (Select Account from Contact Where Name !='')

Best Answer

That can be a difficult query since Accounts and Contacts typically have a lot of records. There's a risk of running into the "Too many query rows" exception, but if you don't have too much data at this point, this query might work for you:

Apex:

list<AggregateResult> arlist = [
    SELECT COUNT(Id), AccountId
    FROM Contact 
    WHERE AccountId != NULL 
    GROUP BY AccountId
    HAVING COUNT(Id) >= 4
];

Rest api: 

$ curl -H 'Authorization: Bearer <session-id>' \
  -H 'X-PrettyPrint: 1' \
  https://ap4.salesforce.com/services/data/v38.0/query?q=SELECT+COUNT%28Id%29,+AccountId+FROM+Contact+WHERE+AccountId+!=+NULL+GROUP+BY+AccountId+HAVING+COUNT%28Id%29+%3E=+4
Related Topic