+ Start a Discussion
Sakthivel ThandavarayanSakthivel Thandavarayan 

How to limit rows in sub query on aggregate result ?

Hi,

I've custom object called csatsurvey__c and have a lookup field contact__c

Multiple surveys can be tied to single contact. I want to calculate average of survey field(numberic) and update it on contact field. 

Below query works fine in the trigger, 
 
select contact__c Id,avg(Client_Service_Overall_Satisfaction__c) overallAverage FROM CSATSurvey__c WHERE contact__c!=null and contact__c in :contactids group by contact__c
however it calculates average of all available related records. I want to include only most recent 5 surveys in calculation. I cannot limit here in the above query as it limits actual aggregate result. 

If I query on contact object I cannot use aggregate function on sub query, getting error - only root queries support aggregate expressions
 
SELECT ID, (Select Id, avg(Client_Service_Overall_Satisfaction__c) from CSATSurveys order by createddate desc LIMIT 5) from Contact Where ID in contactIds

Whats the best way to achive it?
 
SandhyaSandhya (Salesforce Developers) 
Hi Sakthivel,

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.

Please refer below link where you have the solution for the similar scenario.

http://salesforce.stackexchange.com/questions/122226/aggregate-values-with-limits-and-orders


Hope this helps you!

Please accept my solution as Best Answer if my reply was helpful. It will make it available for other as the proper solution. If you felt I went above and beyond, you can give me kudos.


Thanks and Regards
Sandhya