Aggregate Queries: COUNT, SUM, and GROUP BY

The lessons so far picked individual records: filter them, sort them, cap them. Reports usually ask a different question — not which records, but how many, how much, or on average. SOQL answers those with aggregate functions, and it never brings the rows into Apex at all.

Overall Aggregates: One Number for the Whole Query

An aggregate function collapses the matching rows into a single value. With no GROUP BY, the whole query returns one number:

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

That is the only aggregate that assigns straight to a variable. Every other aggregate — and COUNT(field) — comes back as a List<AggregateResult> with a single row, whose values are read positionally as expr0, expr1, and so on:

List<AggregateResult> totals = [SELECT MIN(AnnualRevenue), MAX(AnnualRevenue) FROM Account];
Decimal lowest = (Decimal) totals[0].get('expr0');

COUNT() with no argument counts rows. COUNT(TickerSymbol) counts only the rows where that field is populated, because aggregates skip nulls. SUM and AVG ignore nulls for the same reason, so a column with holes in it averages only the values that are actually there.

GROUP BY: One Row per Group

Add GROUP BY and the query returns one row per distinct value instead of one row overall. The result is a List<AggregateResult>: group keys are readable by field name, and each aggregate is readable by the alias you gave it.

List<AggregateResult> rows = [SELECT Rating, COUNT(Id) total
    FROM Account
    WHERE Type = :accountType
    GROUP BY Rating
    ORDER BY COUNT(Id) DESC];

for (AggregateResult ar : rows) {
    System.debug(ar.get('Rating') + ' has ' + ar.get('total') + ' accounts');
}

WHERE still filters the input rows, and HAVING filters the finished groups (HAVING COUNT(Id) > 10 keeps only the groups of more than ten). When no group matches, the list is empty rather than null — the same LIMIT 0 behaviour you already know.

Three Rules That Bite

Inside a GROUP BY query, COUNT() with no argument is a parse error — the empty parentheses stop the compiler. Always write COUNT(Id) there, and it counts every row in the group.

ORDER BY cannot name an aggregate by its alias. ORDER BY total DESC fails with No such column 'total'; repeat the aggregate expression instead, as in the example above. Ordering by a group key (ORDER BY Rating ASC) also works.

Every plain field in the select list has to be either grouped or aggregated. Selecting Name alongside Rating and COUNT(Id) fails with Field must be grouped or aggregated: Name, which is the database refusing to guess which of the many Accounts in a group you meant.

Common Mistakes

Writing COUNT() inside a grouped query is the most common slip, and it fails at compile time with a bare Unexpected token ')' that does not name the problem. Naming the alias in ORDER BY is the second: the alias is a label on the result row, not a column of the object, so the database cannot sort on it.

A third slip is treating a grouped result as if it were a List<Account>. It is a List<AggregateResult>, and every value comes back through get(...).

Why This Matters

Every dashboard tile, roll-up report, and summary page is one of these queries. Counting with Apex instead would load every row into the org just to count them, and counts are the single most common thing a Lightning component asks its Apex controller to do. Getting the grouping right in SOQL is the difference between one row and thousands.