Group by a parent field

SOQL queries · Intermediate · 14 min · Runs in your Salesforce Developer org · Apex Path

GROUP BY `AccountId` on Contacts and let `COUNT(Id)` roll each parent up. One query returns one summary row per Account, so you avoid a loop that queries one Account at a time. The point is to see how `AggregateResult` and column aliases work together.

Lesson

Why this matters

"How many Contacts does each Account have?" sounds like one query, but a lot of code answers it with one query per Account: find Accounts, loop, query Contacts for each one. That hits governor limits fast. GROUP BY collapses the Contact rows that share an AccountId into one summary row, so one query gives you the whole breakdown.

The smallest working example

List<AggregateResult> rows = [
    SELECT AccountId, COUNT(Id) total
    FROM Contact
    WHERE AccountId IN :ids
    GROUP BY AccountId
];
for (AggregateResult row : rows) {
    Id accountId = (Id) row.get('AccountId');
    Integer total = (Integer) row.get('total');
    System.debug(accountId + ' -> ' + total);
}

One summary row per distinct AccountId. total is an alias so you can read the count by name.

Name the parts

**GROUP BY AccountId** collapses rows that share the same AccountId into one summary row.
**COUNT(Id) total** counts the rows in each group and aliases the column total.
**AggregateResult** is the row shape; every column is Object, so you cast with (Integer) or (Id).
**row.get('AccountId')** reads a group key by its column name.

A common wrong version

for (Account a : [SELECT Id FROM Account WHERE Id IN :createdIds]) {
    Integer total = [SELECT COUNT() FROM Contact WHERE AccountId = :a.Id];
    System.debug(total);
}

One query per Account. With 10 Accounts that is 11 queries, and with 101 it blows the SOQL limit. GROUP BY is one query no matter how many groups there are.

The fix

List<AggregateResult> rows = [
    SELECT AccountId, COUNT(Id) total
    FROM Contact
    WHERE AccountId IN :createdIds
    GROUP BY AccountId
];

One query. Walk the list once, pick out the row for Account A and the row for Account B, and read total from each.

Now you try

The starter seeds two Accounts (A gets three Contacts, B gets one) and gives you createdIds. Run the grouped query, pick out A's count and B's count, and print Group rows:, A count: and B count: in that order.

Learn more

The challenge

Count how many Contacts belong to each of this run's two tagged Accounts.

Examples:

Account A has 3 Contacts → A count: 3
Account B has 1 Contact → B count: 1
Rows returned by the grouped query → Group rows: 2

Requirements:

Uses GROUP BY AccountId should be present: the grouping is on AccountId.
Aliases COUNT should be present: COUNT(Id) total names the column so you can read it.
Group rows: 2 should be printed from rows.size().
A count: 3 should be printed for Account A.
B count: 1 should be printed for Account B.

Starter code

String tag = 'AS-Q318-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
List<Account> accounts = new List<Account>{
    new Account(Name = tag + '-A'),
    new Account(Name = tag + '-B')
};
insert accounts;
List<Contact> contacts = new List<Contact>{
    new Contact(LastName = tag + '-C1', AccountId = accounts[0].Id),
    new Contact(LastName = tag + '-C2', AccountId = accounts[0].Id),
    new Contact(LastName = tag + '-C3', AccountId = accounts[0].Id),
    new Contact(LastName = tag + '-C4', AccountId = accounts[1].Id)
};
insert contacts;
Set<Id> createdIds = new Map<Id, Account>(accounts).keySet();

System.debug('Group rows: 0');
System.debug('A count: 0');
System.debug('B count: 0');

Related drills

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ WITH USER_MODE versus system mode · Multi-level relationships with __r ›