In this article I will explain aggregate functions in SOQL.
Aggregate functions are same as SQL aggregate function. Let’s discuss the following aggregate functions provided by SOQL with example.
- AVG()
- COUNT()
- MIN()
- MAX()
- SUM()
- COUNT_DISTINCT()
Let’s start with AVG():
AVG():
It is used to return average value of a numeric field.
Example:
SELECT avg(NumberOfEmployees) FROM Account.
Use group by when you want to display more result set with number of fields.
Example:
SELECT id,avg(NumberOfEmployees) AvgEmployee FROM Account group by id
In the above query we used avg() with group by, and also created alias name AvgEmployee.
COUNT():
This function returns number rows matching with the query criteria.
Example:
SELECT count() FROM Account where name like 'u%'
SELECT id, count(name) FROM Account group by id
COUNT_DISTINCT()
It returns distinct non-null field values matching the query criteria.
Example:
select id,name,count_distinct(AccountNumber) from account group by id,name
MIN()
It returns the minimum value of a field.
Example:
SELECT id,name,AccountNumber,min(NumberOfEmployees) NumberOfEmployee from account group by id,name,AccountNumber
MAX()
It returns the maximum value of a field.
Example:
SELECT id,name,AccountNumber,max(NumberOfEmployees) NumberOfEmployee from account group by id,name,AccountNumber
SUM()
It returns the total sum of a numeric field.
Example:
SELECT SUM(Amount) FROM Opportunity WHERE IsClosed =true