+ Start a Discussion
Mathew Andresen 5Mathew Andresen 5 

Getting Name and Id from aggregate query

Hi,

I have an aggregate result query that is working, but I need to add another paramenter so I can get the query AND the Id of the Account I'm querying.  But I can't figure out how to do it.  If I add the Id field I get an error.

Thanks,
 
public List<AcctSumList> getAccountSummary60() {
        list<AcctSumList> acctSummary = new List<AcctSumList>();
        List<AggregateResult> getList = [SELECT Account.Name acctName, sum(amount) TotalAmount 
                                         FROM Opportunity 
                                 		 WHERE (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId) 
                                         AND StageName='B. Decision Due' AND CloseDate = THIS_Quarter
                                         GROUP BY Account.Name ORDER BY sum(amount) DESC LIMIT 10];
        for(AggregateResult agg : getList) {
            String AccountName = (string)agg.get('acctName');
             Double TotalAmount = (double)agg.get('TotalAmount');
            acctSummary.add(new AcctSumList(AccountName, TotalAmount));
        }
        return acctSummary;
    }

 
Best Answer chosen by Mathew Andresen 5
Saikishore Reddy AengareddySaikishore Reddy Aengareddy
In your aggregate query see if you can include Id in the Group by Clause (GROUP BY Account.Name,Id).

Any field you would need should be included either in group by clause or it should be aggrgated. See what logically makes sense to include Id in group by clause.

something like...

SELECT Id,Account.Name acctName, sum(amount) TotalAmount 
FROM Opportunity                                           
GROUP BY Account.Name,Id ORDER BY sum(amount) DESC LIMIT 10
        

All Answers

Saikishore Reddy AengareddySaikishore Reddy Aengareddy
In your aggregate query see if you can include Id in the Group by Clause (GROUP BY Account.Name,Id).

Any field you would need should be included either in group by clause or it should be aggrgated. See what logically makes sense to include Id in group by clause.

something like...

SELECT Id,Account.Name acctName, sum(amount) TotalAmount 
FROM Opportunity                                           
GROUP BY Account.Name,Id ORDER BY sum(amount) DESC LIMIT 10
        
This was selected as the best answer
William LópezWilliam López
Hello Mathew,

If you need to get another field just add it to the grouping and it should work, to the the Account Id from the Opportunity should be something like this:
 
public List<AcctSumList> getAccountSummary60() {
        list<AcctSumList> acctSummary = new List<AcctSumList>();
        List<AggregateResult> getList = [SELECT Account.Name acctName, , AccountId, sum(amount) TotalAmount 
                                         FROM Opportunity 
                                 		 WHERE (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId) 
                                         AND StageName='B. Decision Due' AND CloseDate = THIS_Quarter
                                         GROUP BY Account.Name, AccountId ORDER BY sum(amount) DESC LIMIT 10];
        for(AggregateResult agg : getList) {
            String AccountName = (string)agg.get('acctName');
             Double TotalAmount = (double)agg.get('TotalAmount');
            acctSummary.add(new AcctSumList(AccountName, TotalAmount));
        }
        return acctSummary;
    }


Bill,

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
 
Mathew Andresen 5Mathew Andresen 5
Ahh so simple, thanks so much