Apex Aggregate Queries

Apex Aggregate Queries

This skill activates when a practitioner needs to write SOQL aggregate queries inside Apex — covering GROUP BY, GROUP BY ROLLUP, GROUP BY CUBE, HAVING, COUNT/SUM/AVG/MIN/MAX, AggregateResult field access, date grouping functions, and the platform limits unique to aggregate queries.

Before Starting

Gather this context before working on anything in this domain:

• Confirm which object and fields will be aggregated. Numeric fields (Currency, Number, Percent) support SUM/AVG/MIN/MAX. Any field supports COUNT(fieldName).
• The most common wrong assumption is that aggregate queries share the flat-SOQL 50,000-row governor limit. They do not — aggregate queries cap at 2,000 rows regardless of record volume.
• Verify whether GROUP BY ROLLUP or CUBE is needed; those variants generate subtotal rows that count toward the 2,000-row cap and require null-checking in Apex.

Core Concepts

AggregateResult and Field Access

Most SOQL aggregate queries return List<AggregateResult> instead of typed SObject lists (the exception is a bare COUNT() — see below). AggregateResult is a read-only sObject used only for query results; you cannot insert, update, or persist one. Every column in the result set — whether a grouped field or an aggregate function — must be accessed by alias string using get('alias'). There are no typed getters. The return type of get() is Object; cast to the expected type before use.

Aliases are assigned with the alias keyword in SOQL (e.g. SUM(Amount) totalRevenue). When an aggregate function appears without an explicit alias Salesforce auto-assigns names like expr0, expr1 in declaration order. Relying on auto-assigned aliases is fragile — always supply explicit aliases. When the same function appears twice in one SELECT (e.g. COUNT(Id) on two different fields), explicit aliases are mandatory; without them one result overwrites the other.

COUNT() vs COUNT(fieldName)

COUNT() (no argument) and COUNT(fieldName) look interchangeable but differ in three ways — null handling, syntax, and result shape — and conflating them is a common source of invalid queries and runtime casts against the wrong type.

Null handling. COUNT() counts every row that matches the filter, including rows where fields are null — it counts rows, not a specific field's populated values. COUNT(fieldName) counts only rows that have a non-null value for fieldName. The distinction matters when the counted field is optional or sparsely populated. COUNT() and COUNT(Id) are SOQL's equivalent of SQL's COUNT(*).

Syntax. Bare COUNT() must be the only element in the SELECT list — it cannot be combined with other fields, with other aggregate functions, or with a GROUP BY grouping field. SELECT COUNT(), LeadSource FROM Lead GROUP BY LeadSource is invalid; to get a per-bucket count, use COUNT(Id): SELECT LeadSource, COUNT(Id) cnt FROM Lead GROUP BY LeadSource. COUNT() also can't be paired with ORDER BY, though it is compatible with LIMIT. COUNT(fieldName) carries none of these restrictions — you can list several COUNT(fieldName) items alongside other aggregates and grouped fields in one SELECT.

Result shape. A bare COUNT() query does not return an AggregateResult — its result comes back through the QueryResult object's size field (the records field is null), so you assign it straight to an Integer with no iteration:

Integer leadCount = [SELECT COUNT() FROM Lead WHERE Status = 'Open'];

COUNT(fieldName) — like every other aggregate — returns inside an AggregateResult record; an unaliased COUNT(fieldName) lands under the implied alias expr0, so read it with ar.get('expr0') (or supply an explicit alias). Do not iterate a List<AggregateResult> over a bare COUNT() query, and do not assign a COUNT(fieldName) query to a bare Integer.

Governor cost. A COUNT() or COUNT(fieldName) query consumes just one query row against limits — unless it has a GROUP BY, in which case one query row per grouping is consumed.

GROUP BY Variants, HAVING, and the 2,000-Row Cap

GROUP BY fieldA groups result rows by distinct values of fieldA. GROUP BY ROLLUP(fieldA, fieldB) adds subtotal rows for each leading subset of the grouping columns plus a grand total row; ROLLUP aggregates from right to left through the grouping columns, so field order determines which subtotal levels you get. GROUP BY CUBE(fieldA, fieldB) adds subtotals for every combination of grouping columns, useful for cross-tabular reports. Both ROLLUP and CUBE subtotal rows appear as null for the rolled-up dimension in the result — Apex code must null-check before casting (and see GROUPING() below when the grouped field can itself hold nulls).

Two constraints apply to both variants. First, they cap the grouping-field list at three fields (GROUP BY ROLLUP(a, b, c) is the maximum). Second, every grouped field must sit inside the parentheses — you cannot mix plain GROUP BY with GROUP BY ROLLUP/CUBE in one statement. GROUP BY ROLLUP(Region__c), Family is invalid; write GROUP BY ROLLUP(Region__c, Family).

The 2,000-row hard cap applies to the entire result set including subtotal rows generated by ROLLUP/CUBE. If the expected cardinality of grouped results exceeds 2,000, split the query with WHERE filters or switch to Batch Apex.

HAVING filters on aggregate values after grouping (e.g. HAVING SUM(Amount) > 10000). It cannot be replaced by WHERE because WHERE runs before aggregation and cannot reference aggregate function output.

QueryMore (cursor-based pagination) is not supported for aggregate queries. The full result must fit within 2,000 rows.

Detecting Subtotal Rows with GROUPING()

GROUPING(fieldName) is the reliable way to tell a ROLLUP/CUBE subtotal row apart from a detail row. It returns 1 when the row is a subtotal for that field (the field was rolled up, not part of this row's grouping) and 0 when the row carries an actual value for that field. It can appear in the SELECT, HAVING, and ORDER BY clauses, and the value comes back through ar.get('alias') as an Integer.

Add one GROUPING(field) expression per rolled-up field, each with its own alias. In the ROLLUP example below, GROUPING(LeadSource)=0, GROUPING(Rating)=1 marks a per-LeadSource subtotal, and both =1 marks the grand total:

SELECT LeadSource, Rating,
       GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
       COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating)

The naive ar.get('field') == null subtotal check is only safe when the grouped field can never hold a real null. When the grouped field is nullable — LeadSource, an optional picklist, an unset lookup — a genuine null detail row and a subtotal placeholder row are both null, and the null check misclassifies real data as a subtotal. GROUPING() is the only way to separate the two, and it matters most when more than one field is in the ROLLUP or CUBE clause.

Date Grouping Functions

CALENDAR_YEAR(dateField), CALENDAR_MONTH(dateField), DAY_IN_WEEK(dateField), FISCAL_YEAR(dateField), FISCAL_QUARTER(dateField), HOUR_IN_DAY(dateTimeField), and related functions can appear in both SELECT and GROUP BY. They allow time-series grouping without post-processing in Apex. The return type from get('alias') for these functions is Integer. Always pair the function in SELECT with an alias.

GROUP BY Incompatibilities

GROUP BY cannot be used inside inner queries (subqueries). It is also incompatible with WITH DATA CATEGORY. Attempting either causes a compile-time or query parse error.

Common Patterns

Revenue Rollup by Dimension with HAVING Threshold

When to use: Dashboard-style aggregation — e.g. total closed revenue per Account, filtered to accounts with revenue above a threshold.

How it works: Write a GROUP BY query with SUM and a HAVING clause. Cast get('totalRevenue') to Decimal. Null-check before arithmetic.

Why not the alternative: Fetching all Opportunity records and summing in Apex loops burns heap and CPU. The aggregate query shifts computation to the database tier.

Time-Series Grouping with Date Functions

When to use: Monthly or annual trend analysis — e.g. opportunities closed per month over the past year.

How it works: Use CALENDAR_YEAR(CloseDate) and CALENDAR_MONTH(CloseDate) in both SELECT and GROUP BY. Cast the returned values to Integer in Apex. Sort by year and month in the SOQL ORDER BY clause.

Why not the alternative: String-formatting the date in Apex after a flat query requires loading all records and iterating in memory — this blows governor limits at scale.

ROLLUP for Subtotals

When to use: Hierarchical reports — e.g. revenue grouped by Region and Product Family, with subtotals per Region and a grand total.

How it works: GROUP BY ROLLUP(Region__c, Family) produces N+1 grouping levels. Detect subtotal rows by checking if (ar.get('region') == null) — but only when the grouped fields can never hold a real null; otherwise use GROUPING() (see the GROUPING() section above). The grand total row has both grouping fields null.

Decision Guidance

Recommended Workflow

Step-by-step instructions for an AI agent or practitioner working on this task:

1. Define the metric and dimension — identify the object, the numeric field to aggregate (or Id for COUNT), and the grouping dimension(s). Confirm whether the practitioner needs subtotals (ROLLUP/CUBE) or a simple GROUP BY.
2. Estimate result cardinality — if the number of distinct grouped values may exceed 2,000 (factoring in ROLLUP/CUBE subtotal rows), plan a partitioned approach (Batch Apex or WHERE-range splitting) before writing any query.
3. Write the SOQL with explicit aliases — assign an alias to every aggregate function. If the same function is used twice, aliases are required. Prefer descriptive aliases (totalRevenue, oppCount) over expr0.
4. Add HAVING if filtering on aggregate output — do not put aggregate-dependent filters in WHERE. Use HAVING for post-group filtering.
5. Write the Apex iteration — loop over List<AggregateResult>, call ar.get('alias') for each column, cast to the correct type (Decimal for SUM/AVG/MIN/MAX, Integer for COUNT and date functions), and null-check ROLLUP/CUBE subtotal rows.
6. Validate governor limit exposure — confirm the query is not inside a loop, and that the expected row count stays under 2,000. Add a unit test that mocks aggregate results.
7. Run the query in Developer Console — verify aliases match what Apex code expects; confirm HAVING filters produce the expected subset.

Review Checklist

Run through these before marking work in this area complete:

• Every aggregate function in SELECT has an explicit alias
• Apex code uses ar.get('aliasName') with cast — not a typed SObject getter
• Result row count (including ROLLUP/CUBE subtotals) is expected to stay under 2,000
• Filters on aggregate values use HAVING, not WHERE
• ROLLUP/CUBE subtotal rows are distinguished with GROUPING() (not a bare null check) whenever a grouped field is nullable
• No aggregate GROUP BY inside an inner/subquery
• No WITH DATA CATEGORY combined with GROUP BY
• Unit tests cover the aggregate iteration logic (mock or Test.setFixedSearchResults equivalent)

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. 2,000-row cap (not 50,000) — Aggregate queries are subject to a separate 2,000-row governor limit that applies org-wide, not the 50,000-row flat SOQL limit. GROUP BY ROLLUP and CUBE inflate the row count with subtotal rows that count against this same cap. Queries that look safe based on record count can silently fail at runtime when subtotal rows push the result over 2,000.
2. Missing alias causes NullPointerException — Calling ar.get('myAlias') when the query used no alias (or a typo alias) returns null; subsequent casts like (Decimal) ar.get('myAlias') throw a NullPointerException. Auto-assigned aliases (expr0, expr1) can shift silently when the SELECT column order changes. Always use explicit, stable aliases.
3. ROLLUP row count inflation vs actual data — GROUP BY ROLLUP(A, B) produces rows for each (A, B) pair PLUS one row per distinct A PLUS one grand total row. For N groups of A and M groups of B the row count is N×M + N + 1. Practitioners who only consider the base GROUP BY cardinality underestimate total rows and hit the 2,000-row cap unexpectedly.
4. A subtotal placeholder null looks exactly like a real null — ROLLUP/CUBE fill the rolled-up dimension with null on subtotal rows. If the grouped field can also hold a legitimate null (an unset picklist, an empty lookup), a get('field') == null subtotal check treats those genuine detail rows as subtotals and double-counts or mislabels them. Use GROUPING(field) (returns 1 for a true subtotal, 0 for data) to discriminate.
5. Bare COUNT() does not drop into GROUP BY like COUNT(fieldName) — Because most examples use COUNT(fieldName), practitioners assume bare COUNT() behaves the same way and write SELECT COUNT(), LeadSource FROM Lead GROUP BY LeadSource, which is invalid: COUNT() must be the only element in the SELECT list. Its result also arrives differently — through QueryResult.size as a scalar Integer, not an AggregateResult — so code that iterates List<AggregateResult> over a bare COUNT() (or assigns a COUNT(fieldName) query to an Integer) breaks. Use COUNT(Id) when you need a count inside a grouped or multi-column query.

Output Artifacts

Related Skills

• apex-soql-relationship-queries — use when GROUP BY needs to traverse a relationship (parent or child subquery) or when the query is part of a larger join-style pattern
• soql-fundamentals — foundational SOQL syntax, LIMIT/OFFSET, WHERE filters, and flat query governor limits
• batch-apex-patterns — use when aggregate result set may exceed 2,000 rows and requires partitioned execution
• apex-performance-profiling — use when aggregate queries are suspected as a CPU or SOQL-query-count bottleneck