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.

This Tutorial Covers:

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