Avoid too many SOQL calls in APEX Select COUNT()

Question

I am trying to create a count summary of all calls logged by a specific sales rep (as Task) for each of his prospects (e.g. 8 calls for prospect A, 14 for B, etc.) using Apex.
Below is what I got so far, unfortunately, Salesforces complains: "Too many SOQL calls":

List<Contact> prospects = [SELECT id FROM Contact WHERE OwnerId = '00651000003CWWpAAO'];
for(Contact c: prospects){
    List<AggregateResult> countResult = [SELECT COUNT(Activity_Type__c) FROM Task WHERE WhatId = :c.Id AND Activity_Type__c  = 'Call']; 
    System.debug(countResult[0].get('expr0'));
}

What's a better way to approach this and get the desired data?

Answer 1

You can use an additional GROUP BY to get all the values at once.

List<Contact> prospects = [SELECT id FROM Contact WHERE OwnerId = '00651000003CWWpAAO'];
Map<Id, AggregateResult> results = new Map<Id, AggregateResult>(
  [SELECT COUNT(Id) callCount, WhoId Id 
   FROM Task 
   WHERE WhoId = :prospects AND Activity_Type__c = 'Call' 
   GROUP BY WhoId]
);

You can then loop through each contact and get the result from the Map.

Answer 2

You are running a SOQL query in a loop, which violates the first principle of bulkification. Simply move the query out of the loop and use GROUP BY:

SELECT COUNT(Activity_Type__c) count, WhatId
FROM Task
WHERE WhatId IN :prospects AND Activity_Type__c  = 'Call'
GROUP BY WhatId

Then your AggregateResult records will have count and WhatId properties you can access to get the total for each Contact.

(Side note: Are you sure you want to be querying WhatId? Contact relationships should be in WhoId).