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
Gregory512Gregory512 

Query three records, and perform calculations on them

I'm new to apex... I'm trying to query the most three recent records based on a date field and then get the average.. I figure it would look something like this:

 

SELECT Amount__c FROM cObj__c ORDER BY Date__c ASC LIMIT 3

 
I'm not sure what happens next.  What I think I need to do is store each value in a separate variable or store the sum of the query result in a variable so I can get the average.

 

 

If I'm using the right approach here, I want to insert the final calculated value into a field, similar to the "Hello World" Apex example, except "World" is a dynamic value.

 

Thanks for your help, even if it is just to say this is the completely wrong way.

SuperfellSuperfell

You can do the average calc in the query, something like

 

SObject calc = [SELECT AVG(Amount__c) myAverage FROM cObj__c ORDER BY Date__c ASC LIMIT 3];
Double avg = calc.get('myAverage');
// do something with avg.
Gregory512Gregory512

Thanks Simon..

 

I tried a few things, but couldn't get it done.  When using the aggregate function AVG() I can't seem to use ORDER BY, which is critical.

 

Could I do something like this:

 

 

public class avgAmount {

   public static void addAvgAmount(cObject__c[] cobj){

[SELECT Amount__c FROM cObject__c ORDER BY Date__c ASC GROUP BY Account__c LIMIT 3]

 And then do what I need to do with the three amount values... in this case, I want to insert the average of the three Amount__c  values into a field (using a trigger).. I just don't know how to reference the retrieved values:

 

 

for (cObject__c c:cobj){
         if (c.Name != NULL) {
            c.Average__c = (Amount__cVar(0) + Amount__cVar(1) + Amount__cVar(2) / 3);

 

Does that make any sense?  I've got what I want and I know what do with them, I just don't know how.  Thanks for your help!


Greg

 

 
Pradeep_NavatarPradeep_Navatar

You can use aggregate function to get your results:

 

AggregateResult[] groupedResults = [SELECT AVG(Amount__c)avg FROM Employee__c GROUP BY Date__c  LIMIT 3 ];

 

 For more information visit the link given below:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

 

Hope this helps.