Query to Count Child Records in Salesforce SOQL

In Salesforce, relationships between objects can be defined as either master-detail or lookup relationships, and using SOQL, we can query the details of the child records, such as the record count of child records. In this Salesforce tutorial, I will explain how to query and count child records in Salesforce SOQL.

SOQL Queries for Child Records in Salesforce

In Salesforce, objects are related to other objects with a structure where records are linked through parent-child or child-parent relationships. In Salesforce SOQL, a parent-child relationship exists when one object is related to another via a lookup or master-detail relationship.

SOQL allows us to retrieve child records for a parent using relationship queries. These queries use a subquery to fetch child records under the parent.

Counting child records related to a parent record is an example of querying parent-to-child records, such as counting the number of Contacts related to an Account or the number of Opportunities under an Account.

Count the number of Child records in SOQL

To explain the query of counting the child records, we will take an example where Account is a parent, and Contact is the child object.

The examples below show all possible methods and scenarios for counting the child records through SOQL.

1. Use Aggregate Functions for Summarized Counts

We can use aggregate queries when there is a need to count child records grouped by the parent record.

SELECT AccountId, count(Id)
from Contact
group by AccountId

Output:

Now, in the output, we can see the number of contacts for each account record displayed adjacent to the account IDs.

2. Use COUNT() function in Subqueries:

SOQL has the COUNT() function to count child records.

To use the COUNT() function in SOQL subqueries, let’s take an example where we will query the number of users assigned to a permission set.

The traditional method for that should be the syntax below.

SELECT Id, Name, (SELECT count() FROM Assignments)
FROM PermissionSet 
WHERE Id = '0PS5i000007s59DGAQ'

In the above query, the WHERE ID is the Permission set ID, but this method will give the error MALFORMED_QUERY with the statement “COUNT() can only be used with root queries“.

As a solution for this, we can use the aggregate query that will get details about the assignments of a specific Permission Set in Salesforce and group them by the Permission Set’s ID and Name.

SELECT COUNT(Id), PermissionSetId
FROM PermissionSetAssignment
WHERE PermissionSetId IN ('0PS5i000007s59DGAQ')
GROUP BY PermissionSetId

Output:

In the output, you will see the count of users assigned to the permission-set record. This way, we can execute the SOQL sub-queries by using the COUNT() function.

3. Count child records without parent

When we need to count all child records without grouping them by parent, we can run a simple aggregate query. This is helpful when a parent is missing from some records.

SELECT COUNT(Id) 
FROM Contact

Output:

By following the above examples and their syntax, you can efficiently query and count the child records in Salesforce SOQL.

Conclusion

In this Salesforce tutorial, we have learned about SOQL query methods such as parent-to-child relationship queries, aggregate functions, and subqueries, through which we can retrieve details about child records.

You may also like to read:

• SOQL Query in Apex Programming
• Query Tasks, Notes, Attachments by Object Type using SOQL in Salesforce
• Query Salesforce Picklist Field values using SOQL
• Alias Notations in Salesforce SOQL

Abhijeet

Abhijeet is a skilled Salesforce developer with experience in developing and integrating dashboards, data reports, and Salesforce applications. He is also skilled at optimizing processes and flow automation processes, coding, and executing complex project architecture. Read more about us | LinkedIn Profile.