14

I am trying to run a group by query in Apex where I am grouping by a field on a lookup object. For example:

SELECT Account.Type, count(Id)FROM Opportunity GROUP BY Account.Type

I can run this from the Query Editor in the Developer Console, and it runs fine. When I put in in Apex, I can do this:

AggregateResult[] groupedResults
  = [SELECT Account.Type, count(Id)
      FROM Opportunity
      GROUP BY Account.Type];

My problem is trying to get the value of Account.Type in Apex. I tried this:

for (AggregateResult ar : groupedResults)  {
    System.debug('Type' + ar.get('Account.Type'));
    System.debug('Count' + ar.get('expr0'));
}

and I got the error:

System.SObjectException: Invalid field Account.Type for AggregateResult

So how do I get the value of the lookup field?

21

What happens if you alias the field, does that work?

AggregateResult[] groupedResults = [
  SELECT Account.Type type, count(Id) oppCount
  FROM Opportunity 
  GROUP BY Account.Type];

// snip

System.Debug(ar.Get('type'));

Notice that if you are doing an aggregation (GROUP BY) you can create an alias for both the summarized field and for other fields. This is not documented, but is the only way to get this to work. In your GROUP BY field you need to use the full name of the field, but when getting info from the AggregateResults you use the alias.

  • I just tried that and yes, it did work! Thank you! – hamayoun Nov 7 '13 at 22:42
  • 5
    @hamayoun not angling for points but you should make the answer as accepted so that others know the solution in the future! – Matt Lacey Nov 8 '13 at 1:44

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.