Basically, what I am looking to accoplish with the SOQL Query, is to return the average value of the Last N (20) SAT scores from the Case object. The query would essentially look like this:
Select AVG(SAT_Score__c) from Case where SAT_Score__c != null and OwnerId = '005xxxxxxxxxxxx' ORDER BY ClosedDate Limit 20
But running this query yields the following error:
MALFORMED_QUERY: Ordered field must be grouped or aggregated: ClosedDate
I have also tried this using a sub-select, like this:
Select AVG(SAT_Score__c) from Case WHERE Id IN ( SELECT Id FROM Case WHERE SAT_Score__c != null AND OwnerId = '005xxxxxxxxxxxx' ORDER BY ClosedDate DESC LIMIT 20)
And I get this error as the ORDER BY clause is not supported in sub-selects:
expecting a right parentheses, found 'ORDER'
There has to be a way to accomplish what I am looking to do here - it does not seem to be that complex. Thanks in advance.