Aggregate Functions

Use aggregate functions in a GROUP BY clause in SOQL queries to generate reports for analysis. Aggregate functions include AVG(), COUNT(), MIN(), MAX(), SUM(), and more.

You can also use aggregate 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.

1SELECT AVG(Amount)
2FROM 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.

1SELECT CampaignId, AVG(Amount)
2FROM Opportunity
3GROUP BY CampaignId

This table lists all the aggregate functions supported by SOQL.

All aggregate functions ignore null values, except for COUNT() and COUNT(Id).

COUNT(fieldname) is different from COUNT(). COUNT(fieldname) does ignore null values.

Note

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

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:

1SELECT MAX(CreatedDate)
2FROM Account LIMIT 1

SOQL and SOSL Reference

Aggregate Functions