Apex Controller to get the total no of Accounts and Opportunities group by Owner Name

Question

I have a requirement to get a table as below,

I was creating an apex controller as below, but its showing issues, May i know where i get wrong ?

public class AccountController {
    public <Account> getAccountsWithOwners() {
        List<Account> accountsWithOwners = [SELECT Count(Id), Owner.Name, (SELECT(count(Id) FROM Opportunities) FROM Account GROUP BY Owner.Name];
        return accountsWithOwners;                                                              
    }
}

Answer 1

The main issue with the query is that aggregate queries are supported only at root level. Inner query for counting opportunities will not work in this case.

We can execute 2 queries for getting the desired results as below :

1 Query Accounts based on Owner.Name as below :

AggregateResult[] accountResults = [SELECT Count(Id),Owner.Name FROM Account GROUP BY Owner.Name ORDER BY Owner.Name]; 

2 Query Opportunities based on the related account owner field as below :

AggregateResult[] opportunityResults = [SELECT count(id), Account.Owner.Name FROM Opportunity GROUP BY Account.Owner.Name ORDER BY Account.Owner.Name];   

In both the queries we are using the same Owner.name field for aggregating as well as ordering which enables us to conclude that 1st result of query 2 will be the count of opportunities for 1st result of query 1 and same for other results as well.

Edit : Either have separate methods or for returning both results from the method, Using a map to store both query results.

public Map<String, List<AggregateResult>> getAccountsAndOppsWithOwners() {

Map<String, List<AggregateResult>> aggResultsMap = new Map<String, List<AggregateResult>>();

AggregateResult[] accountResults = [SELECT Count(Id),Owner.Name FROM Account GROUP BY Owner.Name ORDER BY Owner.Name]; 
AggregateResult[] opportunityResults = [SELECT count(id), Account.Owner.Name FROM Opportunity GROUP BY Account.Owner.Name ORDER BY Account.Owner.Name];   

aggResultsMap.put('accounts', accountResults);
aggResultsMap.put('opportunities', opportunityResults);

return aggResultsMap;
}