You need to sign in to do that
Don't have an account?
Mathew 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,
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; }
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
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
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:
Bill,
Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.