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.
| 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. |
| 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. . |
| 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
j