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

SOQL aggregate queries return `List<AggregateResult>` instead of typed SObject lists. 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) counts all rows that match the GROUP BY bucket, including rows where most fields are null. `COUNT(fieldName)` counts only rows where `fieldName` is non-null. The distinction matters when the counted field is optional or sparsely populated. `COUNT()` is equivalent to `COUNT_DISTINCT` only when used with no argument and no GROUP BY.

### 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. `GROUP BY CUBE(fieldA, fieldB)` adds subtotals for every combination of grouping columns. Both ROLLUP and CUBE subtotal rows appear as `null` for the rolled-up dimension in the result — Apex code must null-check before casting.

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.

### 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)`. The grand total row has both grouping fields null.

---

## Decision Guidance

| Situation | Recommended Approach | Reason |
|---|---|---|
| Aggregate metric from a single object, result < 2,000 groups | Inline aggregate SOQL in Apex | Simplest; all processing in DB tier |
| Aggregate metric, expected groups > 2,000 | Batch Apex with WHERE range partitioning | Aggregate query row cap is 2,000 |
| Need subtotals without post-processing | GROUP BY ROLLUP or CUBE | Subtotals generated by DB; null sentinel marks subtotal rows |
| Filter on aggregate value (e.g. SUM > X) | HAVING clause | WHERE cannot reference aggregate output |
| Cross-object aggregate (e.g. parent field in GROUP BY) | Use dot-notation field in GROUP BY (e.g. Account.Industry) | Supported in aggregate SOQL; no join needed |
| Time-series grouping | CALENDAR_YEAR/CALENDAR_MONTH in SELECT and GROUP BY | DB-native; avoids heap-heavy Apex date parsing |

---

## 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 null-checked before casting
- [ ] 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.

---

## Output Artifacts

| Artifact | Description |
|---|---|
| SOQL aggregate query | A well-formed query with explicit aliases, HAVING if needed, and correct date functions |
| Apex iteration snippet | Loop over `List<AggregateResult>` with typed casts and ROLLUP null guards |
| Cardinality estimate | Assessment of expected row count vs. the 2,000-row cap |

---

## 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