1

Can some one please confirm if its possible to fetch number of opportunities, number of contacts, from account?

as count() can only be used for root queries so below query is not working.

SELECT Id, Name, (SELECT count() FROM Contacts), (SELECT count() FROM opportunities) FROM Account

can someone please confirm if this is possible by any other mean?

7

You can approach this in two ways. One, you can perform the parent-child subquery and check the size() of the returned list in Apex:

List<Account> accounts = [SELECT Id, Name, (SELECT id FROM Contacts), (SELECT id FROM opportunities) FROM Account];

for (Account a : accounts) {
     System.debug('Count of Opportunities is ' + a.Opportunities.size());
     System.debug('Count of Contacts is ' + a.Contacts.size());
}

n.b. this is an expensive query to run without filters and highly likely to run you into some kind of limits trouble or another.

or two, you can perform an aggregate query against each child object individually:

SELECT count(id), AccountId FROM Opportunity GROUP BY AccountId
SELECT count(id), AccountId FROM Contact GROUP BY AccountId

Both of which will return a list of AggregateResult objects including the Account Id and the count of the child object specified that's linked to that Account.

4

If you are talking about total no. of Opportunity associated with all the accounts in your org:-

SELECT COUNT(Id),  Count(Accountid) From Opportunity

the following query returns the number of opportunities, as well as the number of opportunities associated with an Account.

ultimately, you are getting the no. of opportunity associated with all the account in your org.

simillarly, You can do for Contact:-

SELECT COUNT(Id),  Count(Accountid) FROM Contact

Reference:-https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_count.htm

2

If you're looking to keep this as a single query, then it's helpful to keep in mind that parent-child subqueries return a List<SObject>, which provides us with a size() method.

Integer numContacts = 0;
Integer numOpps = 0;

// Even if you're only expecting a single result, it's still safer to save the
//   results of a query into a List
List<Account> targetAccounts = [SELECT Id, Name, (SELECT Id FROM Contacts), (SELECT Id FROM Opportunities) FROM Account WHERE Id IN :someListOfAccountIds];

for(Account myAccount :targetAccounts){
    // subquery results are a List<SObject> embedded in the object instances of the outer
    //   query.
    // size() is available to us
    numContacts = myAccount.Contacts.size();
    numOpps = myAccount.Opportunities.size();

    // The above will fail after a certain number of records returned by the subquery
    //   (likely due to Salesforce internally making calls to queryMore)
    // The safest way to do this is, unfortunately, iterating over each subquery record
    // Choose one method, or the other
    /*for(Contact c :myAccount.Contacts){
        numContacts++;
    }
    for(Opportunity o :myAccount.Opportunities){
        numOpps++;
    }*/

}

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.