I can write something like this to give me the AccountTeamMembers on all accounts

select Id, (SELECT Id, AccountId FROM AccountTeamMembers) From Account

but I don't know how to just get the count of AccountTeamMembers on the account, as you can't use COUNT in the inner query. I'm ultimately trying to find accounts where there is more than one AccountTeamMember on the Account


You have to go to Aggregate SOQL to do that. You can't filter a parent query based upon a child subquery. You can, however, filter based on a grouping in a child query.

SELECT count(Id), AccountId 
FROM AccountTeamMember 
GROUP BY AccountId
HAVING count(Id) > 1

Here, we go to the AccountTeamMember level, group by AccountId, and then select the AccountId for those groupings of child records that meet our requirements.

For more, see the SOQL and SOSL Reference under GROUP BY and HAVING, as well as Working with SOQL Aggregate Functions.

| improve this answer | |
  • Thanks, I was looking into HAVING but was going about it backwards by trying to use the Account. I appreciate the quick response. – Josh Feb 19 '19 at 19:34

Queries for related data on a parent record (up the heirarchy, say from Opportunity to Account) are represented as an SObject instance.

Likewise, for related data on child records (down the hierarchy, like from Account to AccountTeamMembers, always appearing in an inner query), the data is represented as a List<SObject>.

So Account.AccountTeamMembers is a List<SObject>, and the list class provides a size method. That's a way to get the count you're looking for.

The big caveat here is that there is a not very-well defined point, after which you can't do anything with your child object list other than iterate over it. Best guess is that it's related to calls to queryMore.

The following is the safest (but not the fastest) way to count child records if you're stuck using a parent-child subquery.

Map<Id, List<AccountTeamMember>> accountIdToTeamMembers = new Map<Id, List<AccountTeamMember>>();

for(Account acct :[SELECT Id, (SELECT Id, AccountId FROM AccountTeamMembers) FROM Account]){
    accountIdToTeamMembers.put(acct.Id, new List<AccountTeamMember>());

    for(AccountTeamMember atm :acct.AccountTeamMembers){

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.