Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 36 ยท SOQL and DML

Aggregate SOQL

Summarise data with SOQL aggregate functions COUNT, SUM, AVG, MIN and MAX, GROUP BY, HAVING, ROLLUP and AggregateResult, with Java stand-ins.

Sometimes you do not want the records, only a summary: how many open cases per owner, total revenue per industry, the largest deal this quarter. Aggregate queries make the database do that arithmetic, and return a few summary rows instead of thousands of records. That saves heap, CPU and query rows.

Real SOQL is read-only here. The Java stand-ins compute the same summaries over in-memory data.

Aggregate functions

Function Result
COUNT() Number of rows (returns an Integer)
COUNT(field) Number of rows where the field is not null
COUNT_DISTINCT(field) Number of unique values
SUM(field) Total of a number/currency field
AVG(field) Average
MIN(field) / MAX(field) Smallest / largest value

The simplest form counts rows:

Java
Integer open = [SELECT COUNT() FROM Case WHERE IsClosed = false];

When you use any other function, the result type is AggregateResult, not a record.

Java
AggregateResult r = [SELECT SUM(Amount) total, AVG(Amount) avgAmt, MAX(Amount) biggest
                     FROM Opportunity WHERE IsWon = true];
Decimal total = (Decimal) r.get('total');

The words after the function (total, avgAmt) are aliases. You read each value with get('alias'), and since get returns Object, you must cast it. Without an alias, fields get default names expr0, expr1, ...

GROUP BY

GROUP BY splits the rows into groups and applies the functions to each group:

Java
List<AggregateResult> rows = [
    SELECT Industry, COUNT(Id) cnt, SUM(AnnualRevenue) total
    FROM Account
    WHERE Industry != null
    GROUP BY Industry
    ORDER BY SUM(AnnualRevenue) DESC
];
for (AggregateResult r : rows) {
    System.debug(r.get('Industry') + ': ' + r.get('cnt') + ' accounts, ' + r.get('total'));
}

Every non-aggregated field in SELECT must appear in GROUP BY. This runs in one query and returns one row per industry, no matter how many accounts exist. (Aggregate queries can group over at most 2,000 rows per result, and the rows examined still count against limits.)

HAVING

WHERE filters rows before grouping. HAVING filters the groups after the aggregate is computed:

SQL
SELECT AccountId, COUNT(Id) cnt
FROM Opportunity
GROUP BY AccountId
HAVING COUNT(Id) > 5

Use WHERE to remove rows you never want counted, and HAVING to keep only groups with a certain total.

ROLLUP and CUBE

GROUP BY ROLLUP(field1, field2) adds subtotal rows (and a grand total where the grouped fields are null). GROUP BY CUBE adds subtotals for every combination. GROUPING(field) tells you whether a row is a subtotal. These are handy for reporting but less common in everyday Apex.

Date functions in GROUP BY

You can group by calendar parts: CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate), FISCAL_QUARTER(CloseDate) and DAY_ONLY(CreatedDate):

SQL
SELECT CALENDAR_YEAR(CloseDate) yr, SUM(Amount) total
FROM Opportunity
GROUP BY CALENDAR_YEAR(CloseDate)

Stand-in: GROUP BY with streams

Java's Collectors.groupingBy mirrors GROUP BY. The first stand-in counts and sums per industry; the second adds a HAVING-style filter on the groups.

Java Stand-in (Java): COUNT, SUM, AVG per group

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Java Stand-in (Java): HAVING filters groups after counting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Aggregates and limits

An aggregate query is excellent for dashboards and validation checks, but remember:

  • Aggregate queries do not return record Ids you can update. They return summaries.
  • The number of rows scanned can count toward limits, so filter with selective WHERE clauses.
  • If you need the summary of a related list inside a trigger (for example "update the account's total of open opportunities"), run one grouped aggregate for all accounts in the trigger and put the results in a map. That is the pattern used in trigger bulkification.

Reading results safely

Java
for (AggregateResult r : rows) {
    String industry = (String) r.get('Industry');
    Integer cnt = (Integer) r.get('cnt');
    Decimal total = (Decimal) r.get('total');     // may be null when all values were null
}

SUM and AVG return null when every value in the group is null, so guard before using them in arithmetic. Count aliases come back as Integer; sums of currency come back as Decimal.

Common mistakes

  • Selecting a plain field that is not in GROUP BY.
  • Using WHERE COUNT(Id) > 5 (should be HAVING).
  • Forgetting an alias, then asking for get('cnt').
  • Treating the result as List<Account>.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What type does a query with SUM and GROUP BY return rows as?

    Aggregate queries return AggregateResult rows; read values with get('alias').
  2. 2. Which clause filters groups after aggregation?

    HAVING works on aggregate results; WHERE filters individual rows before grouping.
  3. 3. What does COUNT() with no field return in Apex?

    A lone COUNT() assigns directly to an Integer.
  4. 4. What does SUM return for a group where every value is null?

    Sums of only null values are null, so guard before arithmetic.

Summary

  • Aggregate functions summarise data: COUNT, SUM, AVG, MIN, MAX, COUNT_DISTINCT.
  • Rows come back as AggregateResult; give columns aliases and read them with get plus a cast.
  • GROUP BY makes one row per group; HAVING filters groups; WHERE filters rows before grouping.
  • One grouped query beats many per-record queries.

Next: DML: Insert, Update, Delete and Undelete