SOQL Aggregate Query
Chapter Topics
- SOQL Basics (Salesforce Object Query Language)
- How to Write SOQL in APEX
- SOQL Variable Binding in APEX
- SOQL Keywords
- SOQL Date Literals In Salesforce
- SOQL Aggregate Query
- Child to Parent Relationship
- Parent to Child – Relationship Queries in SOQL
- SOQL Multi level Relationships
- SOQL Return Type
- Salesforce Dynamic SOQL
- SOQL ’for’ Loops
The Salesforce Object Query Language is powerful and allows you to access, manipulate and query your Salesforce data. Any calculations that you may want to perform on data sets, are usually done with the help of SOQL Aggregate Query.
There are various SOQL Aggregate functions such as SUM() and MAX() which allow you to roll up and summarize your data in a query. To use aggregate functions you have to apply GROUP BY clause in SOQL queries to generate reports for analysis. Any query that includes an SOQL aggregate function returns its results in an array of AggregateResult objects.
SOQL Aggregate Functions
The table below list all the SOQL Aggregate Functions:
| Aggregate Function | Description |
|---|---|
| AVG() | Returns the average value of a numeric field. For example: SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId |
| 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%’ |
| 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. |
| 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. |
| MAX() | Returns the maximum value of a field. For example:SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY NameAvailable 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 |
Limitations of SOQL Aggregate Query
SOQL aggregate queries have several limitations. Here we have listed some important ones:
- It is not possible to use aggregate functions in subqueries or nested inquiries.
- Only one object can be queried at a time using aggregate queries.
- There are only a few aggregate functions available, including MIN(), MAX(), AVG(), SUM(), and COUNT().
- You cannot use group by fields or include fields that are not included in the aggregate functions while using GROUP BY.
- There are only 2000 records in the result set. Use the LIMIT clause if the query returns more results.
- Because aggregate searches naturally group the results, you cannot use ORDER BY with them.
- Some types of fields cannot be combined. For instance, formula fields do not allow for aggregation. Salesforce’s governor limitations, such as the total query row limit and CPU time limits, still apply to aggregate queries.
Need more support?
Get a head start with our FREE study notes!
Learn more and get all the answers you need at zero cost. Improve your skills using our detailed notes prepared by industry experts to help you excel.
