SOQL query to display number of opportunities, number of contacts, from account?

Question

Can someone please confirm if it's possible to fetch the enumber of opportunities, and the number of contacts, for an account?

as count() can only be used for root queries so the 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 means?

Answer 1

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.

Answer 2

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

Answer 3

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++;
    }*/

}