Using SOQL Aggregate Functions

Aggregate functions in Salesforce Object Query Language (SOQL)), allow you to roll up and summarize your data in a query. These functions include AVG(), COUNT(), COUNT(fieldName), COUNT_DISTINCT(),  MIN(), MAX(), and SUM(). It is possible to use aggregate functions without using a GROUP BY clause. However, the functions become a very powerful tool to generate reports when you use them with a GROUP BY clause. Examples below show the two scenarios

Example 1

AggregateResult[] agResults = [SELECT CampaignId, AVG(Amount) Avg FROM Opportunity];
Object avgAmount = agResults[0].get(‘Average’);

Example 2:

AggregateResult[] results = [SELECT CampaignId, AVG(Amount) Avg FROM Opportunity GROUPED BY CampaignId];

Note: Any 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.

Aggregate functions are a powerful tool to generate reports when you use them with a GROUP BY clause. For example, you could find the average Amount for all your opportunities by Account

AggregateResult[] results = [SELECT AccountId, SUM(Amount), SUM(ExpectedRevenue) FROM Opportunity GROUP BY AccountId];
for (AggregateResult ar : results) {
System.debug(‘Account ID ‘ + ar.get(‘AccountId’));
System.debug(‘Total amount: ‘ + ar.get(‘expr0’));
System.debug(‘Expected Revenue: ‘ + ar.get(‘expr1’)); }

Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri, where i denotes the order of the aggregated fields with no explicit aliases. The value of i starts at 0 and increments for every aggregated field with no explicit alias. In the above example, SUM(Amount) and SUM(ExpectedRevenue) have no aliases and are represented as expr0 and expr1 respectfully.

Consider, the function below

AggregateResult[] results = [SELECT AccountId, AVG(Amount), SUM(ExpectedRevenue) ExpectedRevenue FROM Opportunity GROUP BY AccountId];

The SELECT statement above shows the three scenarios you will come across working with SOQL Aggregate functions. The AccountID is a field, the AVG(Amount) is an aggregate function without alias while SUM(Amount) has an alias ExpectedRevenue . To work with these fields, iterate over the results using a for loop

for (AggregateResult ar : results)  {
   System.debug('Account ID ' + ar.get('AccountId'));
   System.debug('Average amount: ' + ar.get('expr0'));
   System.debug('Expected Revenue: ' + ar.get('ExpectedRevenue'));
      }  

Note: All non-aggregated fields in the SELECT clause of an aggregate function must be included in the GROUP BY clause.

Reference

Apex Developer Guide
SOQL And SOSL Reference

Leave a Reply