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);

Integer totalAccounts = [SELECT COUNT() FROM Account]; — SELECT COUNT() returns a plain Integer directly — a special case, since most aggregate queries return an AggregateResult instead (next section).
System.debug(totalAccounts); — logs the count — 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'));
}

List<AggregateResult> results = [ — a list of summary rows, not plain records.
SELECT Industry, COUNT(Id) recordCount — COUNT(Id) aliased as recordCount.
FROM Account — the object.
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.
]; — closes the query.
(blank line)
for (AggregateResult result : results) { — loops over the summary rows.
System.debug(result.get('Industry') + ': ' + result.get('recordCount')); — reads each computed value by its alias.
} — closes the loop.

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'));
}

List<AggregateResult> results = [ — opens the query.
SELECT Industry, — the grouping field.
SUM(AnnualRevenue) totalRevenue, — each aggregate function needs an alias to be readable afterward.
AVG(AnnualRevenue) averageRevenue — a second aggregate, its own alias.
FROM Account — the object.
GROUP BY Industry — groups by the same field selected above.
]; — closes the query.
(blank line)
for (AggregateResult result : results) { — loops over the summary rows.
System.debug(result.get('Industry') + ' total: ' + result.get('totalRevenue')); — reads a computed value via get('aliasName') — without an alias, the expression itself becomes an awkward key to reference.
} — closes the loop.

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

Salesforce: ○ Not connected Connect

APEX

Execution Results

Run your code to see the result.

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.

❬ Previous Next ❭