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
Sharad Jain 9Sharad Jain 9 

Issue with Aggregate Results

 I have to count all the tasks or activities for every account record and update a custom account field with number. I have 20 thousand accounts and I have to do it with script not with triggers. I am facing problem while iterating aggregate results. Can anyone help me on this .. Below is my code snippet


Set<Id> Accid=new Set<Id>();
List<Integer> check=new List<Integer>();
for(Account Myacc:[SELECT Id,name FROM Account])
{
Accid.add(Myacc.Id);
}

for(List<AggregateResult> lstAR:[Select Account.name, count(Id) Cnt from Task where Activity_Type__c='member support' and Type_of_Member_Support__c ='Flash consulting' and Account.name=:Accid and AccountId!=null group by Account.name]){
    check.add(lstAR);
    Integer intdata =(integer)check[0].get('Cnt');
    Account acc= new Account();
 acc.Member_Support_Delivered__c=intdata;
}
    

 
Raj VakatiRaj Vakati
Try this code
  
Set<Id> accountIds = new Set<Id>();
    for(Task tsk : trigger.isDelete ? trigger.old : trigger.new) {
        accountIds.add(tsk.AccountId);
    }
    AggregateResult[] groupedResults = [SELECT Count(Id), AccountId FROM Task group by AccountId having AccountId in: accountIds];
    Map<Id,Integer> taksCountMap = new Map<Id,Integer>();
    for (AggregateResult ar : groupedResults)  {
        taksCountMap.put((Id)ar.get('AccountId'),Integer.valueOf(ar.get('expr0')));
    }
    
    List<Account> accUpdLst = new List<Account>();
    //Updating the count field
List<Account> accsTobeUpdate = [SELECT Id,name,Member_Support_Delivered__c FROM Account];

for(Account a : accsTobeUpdate){
	if(taksCountMap.get(a.Id)!=null){
	a.Member_Support_Delivered__c = taksCountMap.get(a.Id) ;
	}
}

 
v varaprasadv varaprasad
Hi Jain,

Try below code : 
  
Set<Id> accIds=new Set<Id>();

for(Account Myacc:[SELECT Id,name FROM Account])
{
accIds.add(Myacc.Id);
}

map<id,account> accMap = new Map<id,account>([SELECT Id,name,Member_Support_Delivered__c FROM Account]);
List<AggregateResult> lstAR = [SELECT Count(Id) cnt, WhatId aid FROM Task 
                               WHERE Activity_Type__c='member support' and Type_of_Member_Support__c ='Flash consulting' and whatId IN :accIds GROUP BY WhatId];

system.debug('==lstAR=='+lstAR);

for(AggregateResult agr : lstAR){
    string accId = (string)agr.get('aid');
    if(accMap.containskey(accId)){
       accMap.get(accId).Member_Support_Delivered__c =  (decimal)agr.get('cnt');
        
    }
        
    
}

update accMap.values();

=========================


Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.

Thanks
Varaprasad
@For SFDC Support: varaprasad4sfdc@gmail.com
Blog: http://salesforceprasad.blogspot.com/

Salesforce latest interview questions  :
https://www.youtube.com/channel/UCOcam_Hb4KjeBdYJlJWV_ZA?sub_confirmation=1
 
Sharad Jain 9Sharad Jain 9
Hey Prasad Thanks for your Help!!
But I have to add one more filter in which I have to  Pull all activities(Tasks) that were created between Account custom field (membership expiration date) and 365 days before that date(Account>Membership expirationdate-365days) . Below filter should apply on tasks while pulling data.
consider only When Task.Activity_Type_C = Member Support AND task.Type_of_Membersupport_C = Flash Consulting. update this count on Account.Membership Support Delivered field.

Instead of using filter here 
List<AggregateResult> lstAR = [SELECT Count(Id) cnt, WhatId aid FROM Task
                         WHERE Activity_Type__c='member support' and Type_of_Member_Support__c ='Flash consulting' and whatId IN :accIds GROUP BY WhatId];

we have to use filter here i guess

Set<Id> accIds=new Set<Id>();
for(Account Myacc:[SELECT Id,name FROM Account])
{
accIds.add(Myacc.Id);
}
 
v varaprasadv varaprasad
Try this : 
  
for(Account Myacc:[SELECT Id,name FROM Account where Membership_expirationdate__c < LAST_N_DAYS:365])
{
accIds.add(Myacc.Id);
}

Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.

Thanks
Varaprasad
@For SFDC Support: varaprasad4sfdc@gmail.com
Blog: http://salesforceprasad.blogspot.com/

Salesforce latest interview questions  :
https://www.youtube.com/channel/UCOcam_Hb4KjeBdYJlJWV_ZA?sub_confirmation=1
Sharad Jain 9Sharad Jain 9
But Prasad I need to find all the Tasks which has been created when Membership_Expiration_Date__c-365 days before and Membership_Expiration_Date__c field is in account.
I am not able to create a soql for this