4

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.

0
3

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];
4
  • I'm thinking you're right here. I'm not coding in APEX in this instance, but I'm thinking I'll pull the results of the simple query into an object, and then work with JavaScript functions to average out the values retrieved and display that. I was hoping for a nice one-lined shortcut, but it looks like it's not going to be that easy.
    – gordo
    May 17 '16 at 22:57
  • Sorry, since I thought you tagged the question with Apex I assumed you were looking for apex code. Hope it works anyway. :) May 17 '16 at 22:58
  • 1
    You don't need to consume two queries if you're going to do post-processing, just include the SAT_Score__c field in your query!
    – Adrian Larson
    May 17 '16 at 23:01
  • 1
    I ended up going with this solution in a sense. I had to take this in two steps. First, I ran a query to return the last N cases. Then I looped through the results and created my own aggregate function to return the average value of SAT_Score__c.
    – gordo
    May 17 '16 at 23:28
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
5
  • The query above shows promise.... as long as you only want it by a specific owner May 17 '16 at 22:59
  • @SebastianKessel It's in the WHERE clause, so I assumed yes (there can only be one matching group).
    – Adrian Larson
    May 17 '16 at 22:59
  • Yup, I assumed the same but wanted to call it out May 17 '16 at 23:00
  • So far so good, thanks! Just verifying some data, but I think this is exactly what I was looking for.
    – gordo
    May 17 '16 at 23:01
  • Doesn't actually look like this is pulling the right data. I run the query against one user and it returns a 9.5. Then I pull the last 20 cases with scores and average them out, and I get a 9. If I add DESC to the end of the ORDER BY clause, it make no difference.
    – gordo
    May 17 '16 at 23:15
1

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.

3
  • 1
    Did you try it? When I did, I got Non-grouped query that uses overall aggregate functions cannot also use LIMIT.
    – Adrian Larson
    May 17 '16 at 22:53
  • @Adrian Didn't try it with LIMIT and agree that doesn't work. Will add that to the answer.
    – Keith C
    May 17 '16 at 22:54
  • I tried this suggestion, and got this error: MALFORMED_QUERY: Non-grouped query that uses overall aggregate functions cannot also use LIMIT
    – gordo
    May 17 '16 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.