+ Start a Discussion
Irvine DelacroixIrvine Delacroix 

How to Sum List value

Hi Guys,

I'm a newbie with Apex coding and I'm trying to summarize the value of List and then have the output to a field in the Account.

My Trigger is on Account object and I'm trying to query all the Contacts and then Sum a number field from contact object. (Like Roll up summary field)

List<Contact> conList = [SELECT NumFieldFromContact__c FROM CONTACT WHERE ID IN: <AccountID>];

Account A has 3 Contacts, and the NumFieldFromContact__c contains 10 value. So when my trigger run, the field under Account Object should be updated with the 30 Value.

Here's what I'm testing in Developer Console.

String accID = '0019000001EnVzf';
List<Contact> conList = [SELECT NumFieldFromContact__c FROM Contact WHERE contact.Account.id = :accID];

Integer i=0;
Double x;
Double y;

Do {
    x = conList[i].Formula_Number_Field__c;
    y = x + x;
    i++;
    
} WHILE (i<conList.size());
    
System.debug(y);

Just experimenting on how things works. Can anyone please give me a correct syntax and Explanation of the Line? :)
Best Answer chosen by Irvine Delacroix
Bhanu MaheshBhanu Mahesh
Hi Irvine,

You can use AggregateResult to get the SUM,AVH,MIN,MAX
Set<Id> accIds = new Set<Id>();
//Populate the above set with Account Ids for which you want to calculate total
List<AggregateResult> lstagr = [SELECT AccountId,SUM(NumFieldFromContact__c )tot FROM Contact WHERE AccountId IN : accs Group By AccountId];

This Query will give you the total of all contacts related to a account

You can get Account Account Id by  (Id)agr.get('AccountId') and Tot for that Account by (Decimal)agr.get('amt') and keep them in a map
 Map<Id,Decimal> mapTot=new Map<Id,Decimal>();
for(AggregateResult agr :lstagr){
    id ids = (Id)agr.get('AccountId');
    Decimal amnt = (Decimal)agr.get('amt');
    mapTot.put(ids,amnt);
}

This map will give you the total for a particular account

Regards,
Bhanu Mahesh

All Answers

Dushyant SonwarDushyant Sonwar
Hi Irvine,
Use Aggregate Query To Achieve your Result...
 
Bhanu MaheshBhanu Mahesh
Hi Irvine,

You can use AggregateResult to get the SUM,AVH,MIN,MAX
Set<Id> accIds = new Set<Id>();
//Populate the above set with Account Ids for which you want to calculate total
List<AggregateResult> lstagr = [SELECT AccountId,SUM(NumFieldFromContact__c )tot FROM Contact WHERE AccountId IN : accs Group By AccountId];

This Query will give you the total of all contacts related to a account

You can get Account Account Id by  (Id)agr.get('AccountId') and Tot for that Account by (Decimal)agr.get('amt') and keep them in a map
 Map<Id,Decimal> mapTot=new Map<Id,Decimal>();
for(AggregateResult agr :lstagr){
    id ids = (Id)agr.get('AccountId');
    Decimal amnt = (Decimal)agr.get('amt');
    mapTot.put(ids,amnt);
}

This map will give you the total for a particular account

Regards,
Bhanu Mahesh
This was selected as the best answer
Bhanu MaheshBhanu Mahesh
Hi Irvine,

To update account using Aggregate query you need one more query to fetch account records to updates. If you are ok with it then use Aggregate result. Otherwise use the below code

I have taken Number field on Contact as testNumber__c and On account as Account_Test__c

trigger listOfContactsOnAccount on contact (after insert , after update,after delete){ 
  set<id> accountIdSet = new set<id>();
  List<Account> lstAccToUpdate = new List<Account>();
  Map<Id,Account> mapAccWithId = new Map<Id,Account>();
  
  if( trigger.isInsert){
    for(contact c : trigger.new){
      if(c.testNumber__c != null){
          accountIdSet.add(c.AccountId);
      }
    }
  }
  
  if(trigger.isUpdate){
      for(contact c : trigger.new){
          if(c.testNumber__c != trigger.OldMap.get(c.Id).testNumber__c){
              accountIdSet.add(c.AccountId);    
          }
      }
  }
  
  if(trigger.isDelete){
    for(contact c: trigger.old){
        if(c.testNumber__c != null){
          accountIdSet.add(c.AccountId);
        }
    }
  }
            
  if(!accountIdSet.isEmpty()){
      for(Account acc :[SELECT Id,Account_Test__c,(SELECT Id,testNumber__c  FROM Account.Contacts) FROM Account WHERE Id IN :accountIdSet]){
          Decimal tot = 0;
          for(Contact con :acc.Contacts){
             tot = tot + con.testNumber__c;      
          }
         acc.Account_Test__c = tot; 
         mapAccWithId.put(acc.Id,acc);
      }
  }
  if(mapAccWithId != null && mapAccWithId.values().size() > 0){
      update mapAccWithId.values();
  }  
}


Regards,
Bhanu Mahesh
Irvine DelacroixIrvine Delacroix
Wow! That was super awesome! I didn't know there's an Aggregate function. It gets me excited with SOQL more :D

I'll play with the codes that you suggested and I'll try to figure out how they really works :)

Is there any other resources I can check about these functions and things about apex that are easy to understand? (Like sfdc99.com) :)

Apex is really exciting and I want to learn more.. I hope you can send me some resources in my email :) vinzell999@gmail.com


Thanks a bunch! @Bhanu Mahesh and Dushyant Sonwar!
Irvine DelacroixIrvine Delacroix
Hi Again,

I noticed that this actually gives different result if the Field that I am referencing is a Formula Number field.

What's the fix for that?

Thanks again! :)
Andrew VuAndrew Vu
Thanks a lot Bhanu Mahesh! Your code is working perfect! 
 
manan patel 7manan patel 7
in above answer which is marke as best answer in that Maptot's value sholde be one degit but it give me a list of values insted sum.