+ Start a Discussion
prasanth sfdcprasanth sfdc 

soql query problem on where clause


   I want to display all contacts.
Conditions:-  We need to display only if the contact parent account has more then 3 contacts. 

So i write this soql. but its not wokring.,  Please help.  If this is done then i need to do the same for custom objects in my project. 

If we write "select count(account.id) from contact group by account.name"  then it is displaying result as excepted. But below query is not working.  
select id,name from contact where (select count(account.id) from contact group by account.name) > 2

NagendraNagendra (Salesforce Developers) 
rajat Maheshwari 6rajat Maheshwari 6

Hi prasanth,

You can use this snippet - 

Map<Id,Integer> mp_Aggregate = new Map<Id,Integer>();

for(AggregateResult aggResult : [Select Count(Id), AccountId from Contact Group by AccountId])

for(Account acc : [Select Id, Name from Account])
     if(mp_Aggregate!=null && mp_Aggregate.containsKey(acc.id))
           if(mp_Aggregate.get(acc.id) > 3)

                // your code placed here


Rajat Maheshwari

v varaprasadv varaprasad
Hi Prashanth

Please use below code .

list<aggregateresult> ar = [SELECT count(Id) cnt, Account.Name name FROM Contact Group By Account.Name LIMIT 100];
system.debug('size = ' + ar.size());

for(aggregateresult a:ar){
    integer total = integer.valueOf(a.get('cnt'));
    if(total > 3){
        system.debug('Account Name ='+ a.get('name') + ' Num Contacts = ' +a.get('cnt')); 

plese check once and let me know if any issues.