function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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