Aggregate Query in Salesforce SOQL

In Salesforce SOQL, Aggregate queries allow us to perform calculations on data sets, such as summing up values, calculating averages, or counting records. We can also perform operations like SUM, COUNT, MIN, MAX, and AVG within a single SOQL.

In this Salesforce tutorial, I will explain how to execute aggregate queries in Salesforce SOQL and their syntax. We will then discuss their use cases with examples.

What is an Aggregated Query in Salesforce?

In Salesforce, aggregate queries calculate summarized information about the organization’s data. Like a SELECT SQL statement, a SOQL query searches the organization database. For example, if you want to find the total revenue, the average deal size, or the number of records in a specific object, you can use aggregate SOQL queries.

Why use aggregate queries in Salesforce?

Aggregate queries in Salesforce perform calculations on a dataset, such as totals, averages, minimum and maximum values, or counts. These queries allow us to process and analyze data efficiently, directly in Salesforce, without requiring additional processing in Apex.

• Efficient Data Processing: Aggregate queries reduce the data retrieved from the database by returning summary information instead of individual records. This minimizes API usage and improves performance.
• Built-In Grouping and Calculation: Aggregate functions like SUM(), COUNT(), AVG(), MIN(), and MAX() allow us to quickly calculate results directly in a SOQL query without adding a custom logic.
• Simplifies Reporting Needs: Aggregate queries help us generate insights or summaries, like the total revenue for a quarter or the count of leads by status, which is often needed for dashboards and reporting.
• Enhanced data visualization: Data fetched from the aggregated queries can be easily visualized through charts, graphs, and other visual representations, making it easier to understand complex data sets and make informed decisions.

Aggregate Functions in Salesforce

Now, we will discuss the following SOQL aggregate functions that work similarly to the SQL queries.

• COUNT(): Counts the number of rows.
• SUM(): Adds up the values in a numeric field.
• AVG(): Calculates the average value of a numeric field.
• MIN(): Finds the smallest value in a numeric field.
• MAX(): Finds the largest value in a numeric field.

Use Case Examples of Aggregate Queries in Salesforce

Now, with the help of examples, we will see the use case of SOQL aggregate queries.

1. COUNT(): The COUNT method returns the output as the total number of records from the database table.

Let’s take an example of executing the aggregate count method, which will get the total number of accounts in Salesforce.

Enter the below SOQL query in the query editor of the Salesforce developer console and execute it.

SELECT COUNT() 
FROM Account

Output:

2. SUM(): This aggregate method returns the total sum of a numeric field.

In the use case of this method, we will calculate the total revenue from closed opportunities using the SUM() function. If the output set contains no rows, it will return NULL.

Enter the below SOQL query in the query editor of the Salesforce developer console and execute it.

SELECT SUM(Amount) 
FROM Opportunity
WHERE StageName = 'Closed Won'

Output:

3. AVG(): This aggregate method returns the average value of a numeric field.

Now, we will run a SOQL query using the AVG() aggregate function to find the average deal size of all closed opportunities.

Enter the below SOQL query in the query editor of the Salesforce developer console and execute it.

SELECT AVG(Amount) 
FROM Opportunity
WHERE IsClosed = true

Output:

4. MAX(): This aggregate method finds the highest value in a specific column. It helps determine the highest of all selected values in a column.

Now, we will use the MAX function to find the highest amount of records from the closed opportunities.

SELECT MAX(Amount) 
FROM Opportunity
WHERE IsClosed = true

5. MIN(): In aggregate methods, the MIN() function is used to find the minimum or lowest value of a column or expression().

Now, we will execute an SOQL query to find the smallest opportunity amount.

SELECT MIN(Amount) 
FROM Opportunity
WHERE IsClosed = true

This way, you can use and execute the above SOQL aggregate methods in your Salesforce instance to write SOQL queries.

While using aggregate queries, we cannot use a LIMIT clause in a query that uses an aggregate function without a GROUP BY clause.

GROUP BY with HAVING Clause

In Salesforce SOQL, we use GROUP BY with the HAVING clause term to apply a condition based on the values of a set of fields.

To understand this, let’s take an example where we have a Contact object, and we will find all accounts where the total number of contacts exceeds 2. We will use the SOQL query below using GROUP BY with the HAVING clause.

SELECT AccountId, COUNT(Id)
FROM Contact
GROUP BY AccountId
HAVING COUNT(Id) > 2

In the above SOQL query, SELECT AccountId, COUNT(Id) retrieves the AccountId and the count of related Contact records for each account.
GROUP BY AccountId groups the results by AccountId, so the count is calculated per account.
HAVING COUNT(Id) > 2 filters the results to include only accounts with more than two contacts.

In the query results, we can see the ID and number of accounts with more than two contacts.

Advantages of Aggregate Query Salesforce

There are various advantages to using the aggregate function in Salesforce SOQL.

• Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() help in summarizing data directly within the query so that we don’t need to process records manually in Apex.
• We can perform calculations like sums or averages directly in the query, simplifying data analysis.
• The GROUP BY clause, combined with aggregate functions, allows for the categorizing and summarizing data. For example, opportunities can be grouped by stage, and total revenue can be calculated for each stage.
• Aggregates reduce the number of records returned and focus only on the required data, such as totals, averages, or counts.

Conclusion

In this Salesforce SOQL tutorial, we have learned the aggregate methods of SOQL; using them, you will be able to optimize your data queries and handle large data sets efficiently. In the above examples, we have seen the execution of the aggregation functions such as SUM(), AVG(), COUNT(), MAX(), and MIN().

You may also like to read.

• DATE Clause in SOQL
• GROUP BY Date Clause in SOQL
• GROUP BY Clause in SOQL
• ORDER BY Clause in SOQL
• Query Salesforce Picklist Field values using SOQL
• Salesforce Relationship Queries in SOQL
• What is the Difference between SOQL and SOSL in Salesforce
• Selective SOQL Queries in Salesforce
• Salesforce SOQL and SOSL Limits for Search Queries

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.