+ Start a Discussion
kreshocskreshocs 

How to select an aggregate query into a map?

To take the example from the docs:

AggregateResult[] groupedResults = [SELECT CampaignId, AVG(Amount) FROM Opportunity 
GROUP BY CampaignId];

Now try

Map<ID, AggregateResult> resultsMap = [SELECT CampaignId, AVG(Amount) 
 FROM Opportunity 
 GROUP BY CampaignId]; 

This won't work even though CampaignId is of type ID. To make it work, alias CampaignId with Id:

Map<ID, AggregateResult> resultsMap = [SELECT CampaignId Id, AVG(Amount) 
 FROM Opportunity 
 GROUP BY CampaignId]; 

Interestingly, the case here does matter. Aliasing with id or ID won't work.

 

This worked on March 28th 2012 (seriously, I swear it did! :-), but does not any more.

 

Please support my idea at https://sites.secure.force.com/success/ideaView?id=08730000000hmhOAAQ to reinstate this behavior. 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

I just tried this in Spring 12 in my Dev Org:

 

Map<Id,AggregateResult> results = new Map<id,AggregateResult>([SELECT AccountId Id, COUNT(Id) ContactCount FROM Contact WHERE AccountId != NULL GROUP BY AccountId]);

This works as expected. Note that aliased Id field cannot be null or you get an exception.

All Answers

Damien_Damien_

If it doesn't work, just loop through the results and manually add it to a Map.

sfdcfoxsfdcfox

Just a regular for-loop would work; you can cast the object into an ID.

kreshocskreshocs

Thanks Damien, sfdcfox, but did you actualy read the message to the end?

 

Damien_Damien_

I did read your message at the end, but it is irrelevant to the answer of your question.

sfdcfoxsfdcfox

I just tried this in Spring 12 in my Dev Org:

 

Map<Id,AggregateResult> results = new Map<id,AggregateResult>([SELECT AccountId Id, COUNT(Id) ContactCount FROM Contact WHERE AccountId != NULL GROUP BY AccountId]);

This works as expected. Note that aliased Id field cannot be null or you get an exception.

This was selected as the best answer
kreshokresho

It works indeed with your extra restriction. Thanks for finding the problem.

kiran kumar 913kiran kumar 913
Map<string,AggregateResult> results = new Map<string,AggregateResult>([SELECT Name, COUNT(Id) AccountCount FROM Account WHERE Name = 'testaccubt90' GROUP BY Name]);

Line: 1, Column: 1
System.ListException: Row with null Id at index: 0
sagar077sagar077
Hello, Please me on this requirement.

Count Account with Different Industry(on account picklist fields is there)(e,g. For Industry Electronics we have 3 Accounts) using Map.

plz help me i am new in apex collection.