Aggregate Function in Salesforce

In this blog you will learn about Aggregate Function in Salesforce.

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