function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JJ746JJ746 

How to retrieve an Id of a record which is retrieved using an aggrgte query?

Navatar_DbSupNavatar_DbSup

Hi,

 

You can use the below sample query on the Account(standard ) object

 

AggregateResult[] groupedResults= [SELECT id, AVG(NumberofLocations__c) FROM Account GROUP BY Id];
for (AggregateResult ar : groupedResults) {
System.debug('Account ID' + ar.get('Id'));
System.debug('Average Locations' + ar.get('expr0'));
}

 

for more detail follow the below link:

 

http://www.salesforce.com/us/developer/docs/apexcode/salesforce_apex_language_reference.pdf

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 
BussBuss

Hi JJ746,

 

i recommend to read Apex book Soql section of "Working with SOQL Aggregate Functions", here i am pasted sample code from Apex Book.

 

AggregateResult[] groupedResults
= [SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId];
for (AggregateResult ar : groupedResults) {
System.debug('Campaign ID' + ar.get('CampaignId'));
System.debug('Average amount' + ar.get('expr0'));
}

 

 

Regs,

Buss
JJ746JJ746

But the query is not grouped by the Id field.

example:  Select Max(Amount) , Opportunity__c from Invoice__c Group By Opportunity__c
JJ746JJ746

Hi Buss,

 

Thanks for your comments.

But the query is not grouped by the Id field.

eg: Select Max(Amount__c), Opportunity__c from invoice__c
where Opportunity__c IN :OpptyIds GROUP BY Opportunity__c

 

Thank You.
JJ746JJ746

Hi,

 

Thanks for your comments.

 

But the query is not grouped by the Id field.

eg: Select Max(Amount__c), Opportunity__c from invoice__c
where Opportunity__c IN :OpptyIds GROUP BY Opportunity__c

 

I want to get the Id of the invoice record with the maximum amount.

Kindly provide me the solution, other than iterating through each record based on Opportunity__c and amount.

 

Thank You.
BussBuss

Hi,

 

i would like know relation between Invoice and Opportunity.

if you are used Master-Detail then there is no Change either you GroupBy Opportunity__c or Id(Invoice), here Check this result using System debug.

if you are used LookUp relation here also you will get same because you are ensuring where clause with Opportunity__c IN :OpptyIds.

 

So my recomendation is GroupBy Id(Invioce) or GROUP BY Opportunity__c results same. Pls Check this Using System debug.

 

 

Reds,

Buss