Aggregate Values with Limits and Orders

Question

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.

Answer 1

The query you issued is not correct, even in SQL. If you're going to order by ClosedDate, then you must also group by it.

From the Aggregate Functions documentation:

You can't use a LIMIT clause in a query that uses an aggregate function, but does not use a GROUP BY clause.

Since you don't want to group... you can't use LIMIT like that.

Instead, you can do it in two steps

List<Case> lastScores = [select Id from CASE order by ClosedDate where SAT_Score__c != null and OwnerId = '005xxxxxxxxxxxx' LIMIT 20];

List<AggregateResult> average = [select AVG(SAT_Score__c) from Case where Id in :lastScores];

Answer 2

I think the following query might do what you need:

SELECT AVG(SAT_Score__c) mean
FROM Case
WHERE SAT_Score__c != null 
AND OwnerId = '005xxxxxxxxxxxx'
GROUP BY OwnerId
ORDER BY MIN(ClosedDate) DESC
Limit 20

Answer 3

The problem is that multiple rows can be merged to create each AVG so you also need to find a way to combined the CreatedDate values of those multiple rows into a single value. I think the only choices are MIN or MAX so use:

ORDER BY MIN(ClosedDate)

or:

ORDER BY MAX(ClosedDate)

in your first query.

PS

As Adrian identified, this doesn't work with the LIMIT term present. So remove that or if its important you will need to find a different answer.