In database management an aggregate function is a function where the values of multiple rows are grouped together to form a single value. In Salesforce SOQL aggregate functions are same as SQL aggregate function.
The following aggregate functions are provided by SOQL:
COUNT() and COUNT(fieldName)
AVG(): Returns the average value of a numeric field.
SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId
COUNT() and COUNT(fieldName): Returns the number of rows matching the query criteria.
COUNT(Id) in SOQL is equivalent to
COUNT(*) in SQL.
COUNT(fieldName) available in API version 18.0 and later. If you are using a GROUP BY clause, use
COUNT(fieldName) instead of
SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'
COUNT_DISTINCT(): Returns the number of distinct non-null field values matching the query criteria.
COUNT_DISTINCT(fieldName) in SOQL is equivalent to
COUNT(DISTINCT fieldName) in SQL. To query for all the distinct values, including null, for an object, see GROUP BY. Available in API version 18.0 and later.
SELECT COUNT_DISTINCT(Company) FROM Lead
MIN(): Returns the minimum value of a field. If you use the
MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order. Available in API version 18.0 and later.
SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName
MAX(): Returns the maximum value of a field.
SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name
SUM(): Returns the total sum of a numeric field.
SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60
Note: You can’t use a LIMIT clause in a query that uses an aggregate function. The following query is invalid:
SELECT MAX(CreatedDate) FROM Account LIMIT 1
The aggregate functions
MAX() in SOQL return an
AggregateResult object or a List of
AggregateResult objects. You can use aggregate functions result in apex by using
List<AggregateResult> result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];