function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
smitha george 5smitha george 5 

Writing a SOQL query

Hi,

Can u write a single SOQL to retrieve list of Accounts(Just Account Ids) which has more than 100 contacts associated to it. (Only one SOQL allowed)
Thanks in Advance
 
LBKLBK
Hi smitha,

I am afraid SOQL is not that sophisticated to have a aggregation sub query).

What you can have is a Rollup Count field in Account to find out the number of contacts under it (you may need a trigger to keep it upto date).

You can use this field to do your filtering.

Hope this helps.
Balayesu ChilakalapudiBalayesu Chilakalapudi
+1 to LBK.
Alternatively, you can query the contacts of each account in a soql and iterate to find it like this, 
for(Account a:[select id, (select id from contacts)]){
  if(a.contacts.size()==100){
      System.debug('account Id having 100 contacts:'+a.Id);
      break;
  }
}
Balayesu ChilakalapudiBalayesu Chilakalapudi
replace line 1 with folllowing 
for(Account a:[select id, (select id from contacts) FROM Account]){
Ahmad J. KoubeissyAhmad J. Koubeissy
You should create a tech field on the account where you calculate the number of related contacts (by trigger). then you can use this field in your query.
  
​Select id from account where tech_field > 100