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.
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.
note: All aggregate functions ignore null values, except for COUNT() and COUNT(Id).
COUNT(fieldname) is different from COUNT(). COUNT(fieldname) does ignore null values.
| Aggregate Function | Description |
|---|---|
AVG() |
Returns the average value of a numeric field. |
COUNT() and COUNT(fieldName) |
Returns the number of rows matching the query criteria. |
COUNT_DISTINCT() |
Returns the number of distinct non-null field values matching the query criteria. |
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. |
MAX() |
Returns the maximum value of a field. Available in API version 18.0 and later. |
SUM() |
Returns the total sum of a numeric field. |
Example AVG():
SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
Example using COUNT():
SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'
Example using COUNT(fieldName):
SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'
Note: COUNT() and COUNT(Id) in SOQL are similar to COUNT(*) in SQL.
Example COUNT_DISTINCT():
SELECT COUNT_DISTINCT(Company)
FROM Lead
Note: COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL. .
Example MIN():
SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName
Example MAX():
SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name
Example SUM():
SELECT SUM(Amount)
FROM Opportunity
WHERE 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:
SELECT MAX(CreatedDate)
FROM Account LIMIT 1