Aggregate Functions

Aggregate functions allow you to roll up and summarize your data for analysis. You can use these functions without using a GROUP BY clause. For example, you could use the AVG() aggregate function to find the average Amount for all your opportunities.

SELECT AVG(Amount)
FROM Opportunity

However, these functions become a more 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 campaign.

SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId

This table lists all the aggregate functions supported by SOQL.

Aggregate Function	Description
`AVG()`	Returns the average value of a numeric field. For example: SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId Available in API version 18.0 and later.
`COUNT()` and `COUNT(fieldName)`	Returns the number of rows matching the query criteria. For example using `COUNT()`: SELECT COUNT() FROM Account WHERE Name LIKE 'a%' For example using `COUNT(fieldName)`: SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%' Note `COUNT(Id)` in SOQL is equivalent to `COUNT(*)` in SQL. The `COUNT(fieldName)` syntax is available in API version 18.0 and later. If you are using a GROUP BY clause, use `COUNT(fieldName)` instead of `COUNT()`. For more information, see `COUNT()` and `COUNT(fieldName)`.
`COUNT_DISTINCT()`	Returns the number of distinct non-`null` field values matching the query criteria. For example: SELECT COUNT_DISTINCT(Company) FROM Lead Note `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.
`MIN()`	Returns the minimum value of a field. For example: SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName 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.
`MAX()`	Returns the maximum value of a field. For example: SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name Available in API version 18.0 and later.
`SUM()`	Returns the total sum of a numeric field. For example: SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60 Available in API version 18.0 and later.

You can't use a LIMIT clause in a query that uses an aggregate function, but does not use a GROUP BY clause. For example, the following query is invalid:

SELECT MAX(CreatedDate)
FROM Account LIMIT 1