Tips: Aggregate function in Salesforce SOQL

Posted in

Salesforce SOQL is a very powerful query language that allows you to retrieve information from Salesforce via API. Similar to other database query language, SOQL do support aggregate function like COUNT(), MAX(), SUM() and etc. However, you will need to use different method to retrieve the result of the aggregate function. See the example below.

 

Query without aggregate function

List myAccounts = [SELECT Id, Name FROM Account];

for(Account acc : myAccounts){
    System.debug( acc.Name );
}

In a normal SOQL, you can retrieve the value by just specifying the field name.

 

Query with aggregate function

ListAggregateResult[] groupedResults = [SELECT COUNT(Id), OpportunityId FROM OpportunityLineItem GROUP BY OpportunityId];

for (AggregateResult ar : groupedResults)  {
    System.debug('Opportunity Id ::: ' + ar.get('OpportunityId'));
    System.debug('Total Line Count ::: ' + ar.get('expr0'));
}

 

As you can see in the code above, you have to store the query result into AggregateResult object. To retrieve the aggregate function field, use the format exprn where n is the position of the aggregate function field in the SOQL.

Get in touch today to see how WDCi can help your business.