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:

• AVG()
• COUNT() and COUNT(fieldName)
• COUNT_DISTINCT()
• MIN()
• MAX()
• SUM()

AVG(): Returns the average value of a numeric field.

Example:

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 COUNT().

Example using COUNT():

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

Example using COUNT(fieldName):

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.

Example:

SELECT COUNT_DISTINCT(Company)
FROM Lead

MIN(): Returns the minimum value of a field. If you use the MIN() or 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.

Example:

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName

MAX(): Returns the maximum value of a field.
Example:

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

SUM(): Returns the total sum of a numeric field.
Example:

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 COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. You can use aggregate functions result in apex by using AggregateResult object.

Example:

List<AggregateResult> result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];