SOQL Aggregate Functions

SOQL Tutorial What is SOQL SOQL Real Life Scenario SOQL Naming Conventions SOQL WHERE SOQL Comparison Operators SOQL Logical Operators SOQL ORDER BY SOQL LIMIT SOQL OFFSET SOQL Aggregate Functions SOQL GROUP BY SOQL HAVING SOQL Relationship Queries SOQL Date Literals SOQL Date Functions SOQL History Objects SOQL Sharing Objects SOQL in APEX Apex Variables in SOQL Queries Static and Dynamic SOQL SOQL return types SOQL Governor Limits Efficient SOQL Queries

The SOQL aggregate functions are used to calculate and return a single value by aggregating the data of a field FROM the returned SOQL records. Aggregate functions are similar to pivot table in Excel.

Aggregate functions can be used to count records, sum numeric field in the returned records, or to return the minimum and the maximum number or date from the returned data.

The SOQL aggregate functions can also be used to generate simplified reports using the GROUP BY clause, by aggregating data in various categories.

For example, you can get the number of clinics for each state, or region.

SELECT Count(Id), State__c
FROM Clinic__c
GROUP BY State__c

Count(ID)	State
120	Florida
10	Illinois
28	Nevada

We get here, the number of clinics for each state. The rows above are also called as aggregated rows.

Let’s take a look at all the aggregate functions supported in SOQL:

SOQL COUNT

COUNT() is used to find the total count of returned rows in SOQL. COUNT(field_name) is used to find the total count of rows which contain non null field values.

Example #1: Find the count of appointments in the previous month.

SELECT Count()
FROM Appointment__c
WHERE Appointment_time__c = LAST_MONTH

Example #2: Find the count of patients who have an email address present in Salesforce.

SELECT count(Email)
FROM Contact

Example #3: Find the count of clinics by various states.

SELECT Count(Id),State__c
FROM Clinic__c
GROUP BY state__c

SOQL AVG

To find the average of values in a field returned by SOQL. This operator can only be used with Number fields.

Example: Find the average booking amount earned by appointments in various clinics in the previous month.

SELECT AVG(booking_amount__c), Clinic__c
FROM Appointment__c
WHERE Appointment_time__c = LAST_MONTH
GROUP BY Clinic__c

SOQL SUM

To find the sum of values in a field returned by SOQL. This operator can only be used with Number fields.

Example: Find the total booking amount earned by appointments in various clinics in the previous month.

SELECT SUM(booking_amount__c), Clinic__c
FROM Appointment__c
WHERE Appointment_time__c = LAST_MONTH
GROUP BY Clinic__c

SOQL MIN

To find the minimal field value in the records returned by SOQL. This operator can only be used with Number fields.

Example: Find the oldest patient’s birthdate for KMG.

SELECT MIN(Birthdate)
FROM Contact

SOQL MAX

To find the mimaximal field value in the records returned by SOQL. This operator can only be used with Number fields.

Example: Find the maximum booking amount charged on a single appointment by each clinic in previous month.

SELECT MAX(booking_amount__c), Clinic__c
FROM Appointment__c
GROUP BY Clinic__c

Important

If a SOQL query contains the >GROUP BY clause, all the fields which are fetched, must either be grouped or aggregated.