SOQL Aggregate functions in Apex

Share on facebook
Share on twitter
Share on linkedin
Aggregate functions in salesforce include AVG(), COUNT(), MIN(), MAX(), SUM().The functions like  SUM() and MAX() in SOQL allow to roll up and summarize the data in a query.

Aggregate functions in salesforce include AVG(), COUNT(), MIN(), MAX(), SUM().The functions like  SUM() and MAX() in SOQL allow to roll up and summarize the data in a query. The GROUP BY clause in a SOQL query is to avoid iterating through individual query results and used to specify a group of records instead of processing many individual records.

  1. AVG() – Returns the average value of a numeric field
  2. COUNT() – Returns the number of rows matching the query criteria
  3. MIN() – Returns the minimum value of a field
  4. MAX() – Returns the maximum value of a field
  5. SUM() – Returns the total sum of a numeric field

A query that includes an aggregate function returns its results in an array of AggregateResult objects. AggregateResult is a read-only sObject and is only used for query results. The values in the AggregateResult object can be accessed much like a map calling a “get” method with the name of the column.

The example Apex Trigger used below is based on the Payment custom object that has a lookup to the Contact and Project objects. Aggregate functions like sum(), max() are used in the query. The sum() is used for the amount field to get the sum of the payment amount made for the project by a contact, max() is to get the last payment date made for the project. The AggregateResult query is used to capture the result of the query and to get the value using an alias name.

Paymentrigger: 

Trigger paymentrigger on payment__c (after insert,after update,after unDelete,after delete) { 
    map<id,project__c> updateMap =new map<id,project__c>(); 
    map<id,contact> updateMap1 =new map<id,contact>(); 
    set<ID> connameset=new set<ID>(); 
    set<ID> projnameset=new set<ID>(); 
    if(Trigger.isInsert||Trigger.isUpdate||Trigger.isUnDelete){ 
        for(payment__c pay:Trigger.new){ 
            if(pay.project__c != null) 
                projnameset.add(pay.project__c); 
            if(pay.Contact__c != null) 
                connameset.add(pay.Contact__c); 
        } 
    } 
    if(Trigger.isDelete){ 
        for(payment__c pays:Trigger.old){ 
            if(pays.project__c!=null) 
                projnameset.add(pays.project__c); 
            if(pays.contact__c!=null) 
               connameset.add(pays.contact__c);             
        } 
    }     
    List<AggregateResult> AggregateResultList = [select project__c,Sum(amount__c)amt,max(Payment_date__c) maxDate from payment__c where project__c in:projnameset group by project__c]; 
    if(AggregateResultList != null && AggregateResultList.size() > 0){ 
        for(AggregateResult aggr:AggregateResultList){             
            project__c pr1=new project__c(); 
            pr1.recentpay__c=(date)aggr.get('maxDate'); 
            pr1.Id=(id)aggr.get('project__c'); 
            pr1.total_amount__c=(decimal)aggr.get('amt'); 
            updateMap.put(pr1.Id, pr1); 
        } 
    }else { 
        for(id idSet:projnameset){ 
            project__c pr1=new project__c(); 
            pr1.recentpay__c=null; 
            pr1.Id=idSet; 
            pr1.total_amount__c=0; 
            updateMap.put(pr1.Id, pr1); 
        } 
    } 
    update updateMap.values();     
    list<AggregateResult> AggregateResultList1=[select Contact__c,sum(amount__c)amts from payment__c where Contact__c in:connameset group by Contact__c]; 
    if(AggregateResultList1!= null && AggregateResultList1.size() > 0){ 
        for(AggregateResult agg:AggregateResultList1){ 
            Contact con=new Contact(); 
            con.Id=(id)agg.get('Contact__C'); 
            con.total_amount__c=(decimal)agg.get('amts'); 
            updatemap1.put(con.Id, con); 
        } 
    }else{ 
        for(id idset1:connameset){ 
            Contact con=new Contact(); 
            con.Id=idset1; 
            con.total_amount__c=0; 
            updatemap1.put(con.Id, con); 
        } 
    } 
    update updatemap1.values();     
} 
Soql Functions
Apex Functions

The above screenshots show two payment records and the red highlight area shows the payment date and the amount paid for the project bsnl by the contact karthi.

Soql Aggregate Functions

The above screenshot shows the project record with recent pay field with the date retrieved from the aggregate result of the query by using a max() to get the last payment date made for the project bsnl. The total amount field shows the total amount paid for the project by using the sum() function.

Reference Link: 

1. https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

2. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm

3. https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_query_aggregateresult.htm

About MST

At MST Solutions our cornerstone is to adapt, engage and create solutions which guarantee the success of our clients. The talent of our team and experiences in varied business verticals gives us an advantage over other competitors.

Recent Articles

Mobile Responsive Testing

Mobile Responsive Testing is simply a Testing Process performed to make sure that your website or an app is working appropriately on all types of devices.
Whether it an app or website, they need to ensure that they are accessible from any device, anytime, anywhere.

Read Article »

Work with us.

Our people aren’t just employees, they are key to the success of our business. We recognize the strengths of each individual and allow them time and resources to further develop those skills, crafting a culture of leaders who are passionate about where they are going within our organization.