Aggregate SOQL cannot get average

Question

So I've read the following:

• Can I assign Aggregate SOQL Result directly to variable?
• Using AggregateResult in Salesforce.com SOQL
• Soql avg,sum,max error in apex program
• Working with SOQL Aggregate Functions

And I cannot get this to work for anything. The field I am trying to aggregate is Time_Spent_minutes__c from our IT Request ticketing system. Time_Spent_minutes__c is a formula field that gives the total amount of time spent on a single request between to date/time fields as minutes. I'm trying to collect all of these within a given time range (say a week), and then average them out into a single field in the Summary_Reporting__c object. I cannot for the life of me figure out why all I get returned is a blank field value.

I would appreciate some advice. Thanks!

//Attempt 3
Trigger VS_Summarizer on Summary_Reporting__c (after insert, after update)
{   
List<Summary_Reporting__c> toUpdate = new List<Summary_Reporting__c>();
for(Summary_Reporting__c sumrep : Trigger.new)
{
    if(sumrep.Object_to_Summarize__c == null && sumrep.Value__c == null)
    {
        Decimal avgAmount = (Decimal)[SELECT AVG(Time_Spent_minutes__c)aver
                                            FROM IT_Request__c
                                            WHERE Date_Closed__c >= : sumrep.Period_Start__c and Date_Closed__c <= : sumrep.Period_End__c group by Date_Closed__c][0].get('aver');

        sumrep.Value__c = avgAmount;
        toUpdate.add(sumrep);
    }
    update toUpdate;
  }

}

Below is a bit more convoluted, but it's where I was originally. Then I worked my way to just trying to make it work with a single trigger (the above).

//Attempt 2

Trigger VS_Summarizer on Summary_Reporting__c (after insert, after update)
{
summarizerLibrary sl = new summarizerLibrary(); 
for(Summary_Reporting__c sumrep : Trigger.new)
  {
      sl.averagerOfITReqs(sumrep)
  }



public class VS_Summarizer_library

{
public void averagerOfITReqs(Summary_Reporting__c sumrep)
{
    if(sumrep.Object_to_Summarize__c == null && sumrep.Value__c == null)
    {
        if(sumrep.Type__c == 'AVG')
        {
            AggregateResult[] groupedResults = [SELECT AVG(Time_Spent_minutes__c)aver
                                                    FROM IT_Request__c
                                                    WHERE Date_Closed__c <= : sumrep.Period_Start__c and Date_Closed__c >= : sumrep.Period_End__c group by Date_Closed__c];
            double avg = double.valueOf(groupedResults[0].get('Time_Spent_minutes__c'));

            sumrep.avgValue__c = avg;

Decimal avgAmount = (Decimal)[SELECT AVG(Time_Spent_minutes__c)aver
                                            FROM IT_Request__c
                                            WHERE Date_Closed__c <= : sumrep.Period_Start__c and Date_Closed__c >= : sumrep.Period_End__c group by Date_Closed__c][0].get('aver');
            sumrep.Test__c = avgAmount;
        }
        update sumrep;
    }
  }
}

Resolution notes:

I implemented the suggestions from the answer, and also thought hard about Adrian's question. Once I made the tweaks and put the date ranges back to an appropriate date range it immediately started working.

Thanks for the help!

Answer 1

I would like to suggest two things,

1. Make sure that the record you are inserting must have all fields values populated which are used in the formula field.
2. Condition specified in WHERE clause retrieves correct records.

Also try using .get('expr0') instead of .get('aver').

Answer 2

Make sure there is actually data matching your filters. If you are looking at a filter like:

WHERE Date_Closed__c >= :s.Period_Start__c and Date_Closed__c <= :s.Period_End__c

you will only get data if there are records between those two values.