Introduction

With the help of SOQL, we can summarize, roll up and retrieve sObjects data from Salesforce. The aggregate functions allow us to count the rows for specific criteria, and when it is coupled with the GROUP BY clause, aggregate functions become more powerful and handy to generate reports.

What is SOQL?

SOQL is Salesforce Object Query Language which is used to query or retrieve records from sObjects in Salesforce. We have multiple functions in SOQL, which are known as SOQL Aggregate Functions.

SOQL Aggregate Functions with Examples

Aggregate Functions in SOQL allow us to summarize the data in a query. We can use aggregate functions with or without the ‘GROUP BY’ clause.

The output of the SOQL query with aggregate functions is the list of aggregate results or an array of aggregate results, not the list of sObjects.

Example: AggregateResult[] groupedResults = [SELECT AVG(Amount) avgAmt, MAX(Amount) maxAmt FROM Opportunity];

In the above query, we have used two aggregate functions with their aliases.

Here, we have multiple aggregate methods in salesforce mentioned below:

1. SUM(): It returns the summation of a numeric field.
Example: AggregateResult[] sumResult = [SELECT SUM(Balance__c) FROM Contact WHERE AccountId != null];

2. MIN(): It gives us the minimum value of a field
Example: AggregateResult[] minResult = [SELECT MIN(Days_to_Close__c) FROM Opportunity];

3. MAX(): It gives us the maximum value of a field
Example: AggregateResult[] maxResult = [SELECT MAX(Days_to_Close__c) FROM Opportunity];

4. AVG(): It gives us the average value of a numeric field
Example: AggregateResult[] avgResult = [SELECT AccountId, AVG(Amount) FROM Opportunity GROUP BY AccountId];

5. COUNT() or COUNT(fieldName): It gives the count of the total records or rows matching the query condition
Example: AggregateResult[] countResult = [SELECT AccountId, COUNT(Id) FROM Contact GROUP BY AccountId];

6. COUNT_DISTINCT(fieldName): It returns the number of distinct records which match the query criteria
Example: AggregateResult[] countDistinctResult = [SELECT COUNT_DISTINCT(NAME) FROM Account];