Apex Aggregate Functions Simplified

Apex Aggregate functions let you aggregate values in your SOQL query.

Suppose you wanted to know the total amount of all the opportunities for a given account or you wanted to know what is the count of Account records in your Salesforce org. All such queries can be performed by Aggregate functions.

Queries that use the Aggregate Functions return a list of AggregateResult objects.

Below is the list of Aggregate Functions which can be used in SOQL :

• AVG
• COUNT
• COUNT_DISTINCT
• MIN
• MAX
• SUM

You can use Aggregate functions with or without Group by Clause.

Let’s use each of these aggregate functions in SOQL with an example.

AVG

It gives you an average value of a field with respect to fetched records.

For Example, Find the average amount of opportunities present in the GenePoint Account.

List<AggregateResult> groupedResults = [SELECT AVG(Amount)average FROM Opportunity WHERE Account.Name = 'GenePoint'];
Decimal avgAmount = (Decimal)groupedResults[0].get('average');
System.debug('Average Amount: ' + avgAmount);

COUNT

It gives you the count of fetched records.

For Example, Find the total count of opportunities in the GenePoint Account.

List<AggregateResult> groupedResults = [SELECT Count(Id) accountCount FROM Opportunity WHERE Account.Name = 'GenePoint'];
Integer accountCount = (Integer)groupedResults[0].get('accountCount');
System.debug('Account Count: ' + accountCount);

COUNT_DISTINCT

It gives you the counts of distinct values for a field present in the fetched records.

For Example, Find the total count of disctint Account present with respect to Name.

List<AggregateResult> groupedResults = [SELECT COUNT_DISTINCT(Name) distinctAccountCount FROM Account];
Integer distinctAccountCount = (Integer)groupedResults[0].get('distinctAccountCount');
System.debug('Distinct Account Count: ' + distinctAccountCount);

MIN

It gives you the minimum value of a field in the fetched records.

For Example, Find the minimum amount for all the opportunities in the GenePoint Account.

List<AggregateResult> groupedResults = [SELECT Min(Amount) minAmount FROM Opportunity WHERE Account.Name = 'GenePoint'];
Decimal minAmount = (Decimal)groupedResults[0].get('minAmount');
System.debug('Min Amount: ' + minAmount);

MAX

It gives you the maximum value of a field in the fetched records.

For Example, Find the maximum amount for all the opportunities in the GenePoint Account.

List<AggregateResult> groupedResults = [SELECT Max(Amount) maxAmount FROM Opportunity WHERE Account.Name = 'GenePoint'];
Decimal maxAmount = (Decimal)groupedResults[0].get('maxAmount');
System.debug('Max Amount: ' + maxAmount);

SUM

It gives you the maximum value of a field in the fetched records.

For Example, Find the total sum of the amount for all the opportunities in the GenePoint Account.

List<AggregateResult> groupedResults = [SELECT Sum(Amount) sumAmount FROM Opportunity WHERE Account.Name = 'GenePoint'];
Decimal sumAmount = (Decimal)groupedResults[0].get('sumAmount');
System.debug('Sum Of Amount: ' + sumAmount);

In all the above queries using aggregate functions, we have typecast the groupedResults[0].get() because it returns an Object and we have to cast it back to the specific type.

We have also used aliasing in the aggregate functions which are the word just following the aggregate functions. In the above block sumAmount just after the Sum(Amount) is an alias.

Declaring an alias is optional and if you don’t declare it then 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. 

For Example, Using aggregate functions without alias:

List<AggregateResult> groupedResults = [SELECT Sum(Amount) FROM Opportunity WHERE Account.Name = 'GenePoint'];
Decimal sumAmount = (Decimal)groupedResults[0].get('expr0');
System.debug('Sum Of Amount: ' + sumAmount);

Apex Aggregate Functions Governor Limits Considerations:

Queries that include Aggregate functions are subject to the same governor limits as other SOQL queries for records returned.

It includes the number of records included in aggregation not just the total number of records returned by the query. This says that if for an aggregation there were 2000 records fetched and in the final aggregated response you get a single row with the aggregated value. The total number of rows consumed would be 2000 only for governor limits.

For COUNT() or COUNT(fieldname) queries, limits are counted as one query row unless the query contains a GROUP BY clause, in which case one query row per grouping is consumed

Thank you for visiting SalesforceBlue.com
If you have any queries feel free to write down a comment below 🙂