[SalesForce] Reference fields on parent object in aggregate query

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?

Best Answer

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.

Related Topic