Drills › Query the org (SOQL)

Summarize Opportunity amounts

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

Scenario. A pipeline summary needs both the number of Opportunities and statistics for populated Amounts.

Compare row and non-null counts, then calculate SUM and AVG for tagged Opportunities.

What you will learn

Aggregate functions summarize rows instead of returning them. COUNT() counts rows. COUNT(Amount), SUM(Amount) and AVG(Amount) skip null Amounts.

AggregateResult row = [SELECT SUM(Amount) total FROM Opportunity];
Decimal total = (Decimal) row.get('total');

Gotcha. COUNT() must be alone in its SELECT, and it returns an Integer. A value with no alias is read as expr0, then expr1, and so on.

The task

Summarize three tagged Opportunities, including one with a null Amount.

Examples:

• three Opportunity rows → Aggregate rows: 3
• Amounts 100, 300 and null → Aggregate nonnull: 2
• the same Amounts → sum 400 and average 200 → Aggregate sum: true and Aggregate average: true

Requirements:

1. Keep the setup. Its getDescribe() line reads the first Stage value in your org, so the insert works whatever your Stages are called.
2. Count all rows with a separate SELECT COUNT() query, filtered to AccountId = :parent.Id.
3. In one more query, get COUNT(Amount), SUM(Amount) and AVG(Amount). Read each with get() and cast it: Integer for the count, Decimal for the others.
4. Print the two counts, then whether the sum is 400 and the average is 200. Print Aggregate rows:, Aggregate nonnull:, Aggregate sum: and Aggregate average:.

Starter code

String tag = 'AS-Q310-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
String stageName = Opportunity.StageName.getDescribe().getPicklistValues()[0].getValue();
Account parent = new Account(Name = tag);
insert parent;
List<Opportunity> seeded = new List<Opportunity>{
    new Opportunity(Name = tag + '-100', AccountId = parent.Id, StageName = stageName,
        CloseDate = Date.today(), Amount = 100),
    new Opportunity(Name = tag + '-300', AccountId = parent.Id, StageName = stageName,
        CloseDate = Date.today(), Amount = 300),
    new Opportunity(Name = tag + '-Null', AccountId = parent.Id, StageName = stageName,
        CloseDate = Date.today(), Amount = null)
};
insert seeded;

Integer rowCount = 0;
Integer amountCount = 0;
Decimal totalAmount = 0;
Decimal averageAmount = 0;
System.debug('Aggregate rows: ' + rowCount);
System.debug('Aggregate nonnull: ' + amountCount);
System.debug('Aggregate sum: ' + (totalAmount == 400));
System.debug('Aggregate average: ' + (averageAmount == 200));

When it passes

All three rows count, while the null Amount is skipped by the field count, the sum and the average.

Try this drill free

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

‹ Find Accounts by Contact · Keep busy Account groups ›