Aggregate Functions in salesforce

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.

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