Drills › Query the org (SOQL)

Keep busy Account groups

Query the org (SOQL) · Intermediate · 18 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A sales summary should show only Accounts with enough pipeline activity to review.

Group tagged Opportunities by Account and keep groups with at least two rows.

What you will learn

GROUP BY returns one row per value. HAVING then filters those groups.

for (AggregateResult row : [
    SELECT AccountId, COUNT(Id) deals FROM Opportunity
    GROUP BY AccountId HAVING COUNT(Id) > 1
]) {
    Id accountId = (Id) row.get('AccountId');
    Integer deals = (Integer) row.get('deals');
}

Gotcha. WHERE filters rows before grouping. HAVING filters groups after counting. A grouped field with no alias is read by its own name.

The task

Group this run's Opportunities by Account and keep only Accounts with two or more.

Examples:

• the Account with 3 Opportunities → kept → Groups high: true
• the Account with exactly 2 (the edge case) → kept → Groups exactly two: true
• the Account with 1 → left out → Groups below excluded: true

Requirements:

1. Query the seeded Accounts' Opportunities with AccountId and COUNT(Id), grouped by AccountId.
2. Keep groups with 2 or more rows using HAVING (>= 2 and > 1 both work).
3. Loop over the groups. Read the Account with row.get('AccountId') and the count by its alias, and put them in countsByAccount.
4. Print how many groups were kept and the three checks. Print Groups kept:, Groups high:, Groups exactly two: and Groups below excluded:.

Starter code

String tag = 'AS-Q311-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
String stageName = Opportunity.StageName.getDescribe().getPicklistValues()[0].getValue();
List<Account> parents = new List<Account>{
    new Account(Name = tag + '-Three'),
    new Account(Name = tag + '-Two'),
    new Account(Name = tag + '-One')
};
insert parents;
insert new List<Opportunity>{
    new Opportunity(Name = tag + '-A1', AccountId = parents[0].Id,
        StageName = stageName, CloseDate = Date.today()),
    new Opportunity(Name = tag + '-A2', AccountId = parents[0].Id,
        StageName = stageName, CloseDate = Date.today()),
    new Opportunity(Name = tag + '-A3', AccountId = parents[0].Id,
        StageName = stageName, CloseDate = Date.today()),
    new Opportunity(Name = tag + '-B1', AccountId = parents[1].Id,
        StageName = stageName, CloseDate = Date.today()),
    new Opportunity(Name = tag + '-B2', AccountId = parents[1].Id,
        StageName = stageName, CloseDate = Date.today()),
    new Opportunity(Name = tag + '-C1', AccountId = parents[2].Id,
        StageName = stageName, CloseDate = Date.today())
};

List<AggregateResult> groups = new List<AggregateResult>();
Map<Id, Integer> countsByAccount = new Map<Id, Integer>();
System.debug('Groups kept: ' + groups.size());
System.debug('Groups high: false');
System.debug('Groups exactly two: false');
System.debug('Groups below excluded: false');

When it passes

The groups with three rows and exactly two rows stay. HAVING removes the one-row group.

Try this drill free

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

‹ Summarize Opportunity amounts · Loop over Accounts in chunks ›