Intermediate 30 min read

Aggregate Functions and GROUP BY

By the end of this lesson, you'll be able to:

Use COUNT, SUM, AVG, MIN, and MAX to summarize query results
Group results by a field using GROUP BY
Read an AggregateResult and access its computed values

Prerequisites: "Child Relationship Queries (Subqueries)"

A simple aggregate: COUNT

Integer totalAccounts = [SELECT COUNT() FROM Account];
System.debug(totalAccounts);

SELECT COUNT() returns a plain Integer directly — a special case, since most aggregate queries return an AggregateResult instead (next section). This is the fastest way to answer "how many records match?" without pulling any actual record data at all.

Grouping with GROUP BY

List<AggregateResult> results = [
    SELECT Industry, COUNT(Id) recordCount
    FROM Account
    GROUP BY Industry
];

for (AggregateResult result : results) {
    System.debug(result.get('Industry') + ': ' + result.get('recordCount'));
}

GROUP BY Industry produces one row per distinct Industry value, each with its own COUNT(Id) — this is Module 17's "headcount by department" report pattern, computed by the database itself rather than looped over in Apex.

SUM, AVG, MIN, MAX

List<AggregateResult> results = [
    SELECT Industry,
        SUM(AnnualRevenue) totalRevenue,
        AVG(AnnualRevenue) averageRevenue
    FROM Account
    GROUP BY Industry
];

for (AggregateResult result : results) {
    System.debug(result.get('Industry') + ' total: ' + result.get('totalRevenue'));
}

Each aggregate function needs an alias (totalRevenue, averageRevenue) to be readable afterward via get('aliasName') — without an alias, the expression itself becomes an awkward key to reference.

Exercise

Write a query grouping Opportunity by StageName, counting how many opportunities are in each stage, aliased as stageCount.

Show hint

SELECT StageName, COUNT(Id) stageCount FROM Opportunity GROUP BY StageName

APEX

Aggregate Functions and GROUP BY Quiz

1. Why does an aggregate function like SUM(AnnualRevenue) usually need an alias?

So the computed value can be read afterward via a clear name with get() Aliases are required by Apex syntax for every field Without an alias, the query fails to compile Aliases make the query run faster

My Notes

Questions about this lesson

No questions yet — be the first to ask.

Summary

Aggregate functions compute a summary value — a count, a total, an average — directly in the query, and GROUP BY produces that summary per distinct value of a field.

← Child Relationship Queries (Subqueries) SOQL For Loops →