aggregate soql select contact fields from opportunity query

Question

wondering if anyone can help me with a query on opportunity that contains contact fields. i've tried a bunch of solutions but no luck yet.

Select count(Id), Account.Contact.SomeField from Opportunity group by Account.Contact.SomeField;

Answer 1

You will not be able to do it this way as there is no direct relationship between the Opportunity -> account -> and a single account.

You have two options:

1. Add a Contact lookup on the opportunity then you could:

   Select count(Id), Contact__r.SomeField from Opportunity group by Contact__r.SomeField;
   

2. Get a list of contacts with the Opportunity.AccountID and do something with them:

   Map<ID,Contact[]> accConMap = New Map<ID,Contact>();
   
   Set<ID> oppAccIDs = New Set<ID>();
   
   for(Opportunity o : SOMEOPPLIST){
      oppAccIDs.add(o.AccountID);
   }
   
   for(Contact c : [Select AccountID, ID From Contact Where AccountID IN :oppAccIDs]){
   
      if(!accConMap.containsKey(c.AccountID))
         accConMap.put(c.AccountID,New Contact[]{});
   
      accConMap.get(c.AccountID).add(c);
   }
   
   //Do Something