SOQL Aggregate Functions and GROUP BY

Totals, averages and counts per category don't need a loop over thousands of records. SOQL can calculate them in the database and return just the summary – faster, lighter on heap, and often a single query instead of many.

Counting

SampleData.create();

Integer total = [SELECT COUNT() FROM Contact];                  // returns an Integer
System.debug('Contacts: ' + total);

// COUNT(field) counts non-null values and returns AggregateResult
AggregateResult ar = [SELECT COUNT(Email) withEmail, COUNT(Id) everyone FROM Contact];
System.debug('With email: ' + ar.get('withEmail') + ' of ' + ar.get('everyone'));

Contacts: 5
With email: 3 of 5

SUM, AVG, MIN, MAX

SampleData.create();

AggregateResult stats = [
    SELECT SUM(Amount) total, AVG(Amount) average, MIN(Amount) smallest, MAX(Amount) largest
    FROM Opportunity
    WHERE IsClosed = false
];
System.debug('Open pipeline: ' + stats.get('total'));
System.debug('Average deal:  ' + ((Decimal) stats.get('average')).setScale(2));
System.debug('Range: ' + stats.get('smallest') + ' – ' + stats.get('largest'));

Open pipeline: 265000.0
Average deal:  132500.00
Range: 15000.00 – 250000.00

Values come back as Object through get('alias'); cast them to Decimal, Integer or Date to use them. Without an alias, the keys are expr0, expr1… – always alias for readable code.

GROUP BY

SampleData.create();

for (AggregateResult row : [
    SELECT StageName stage, COUNT(Id) deals, SUM(Amount) amount
    FROM Opportunity
    GROUP BY StageName
    ORDER BY SUM(Amount) DESC
]) {
    System.debug(row.get('stage') + ': ' + row.get('deals') + ' deal(s), ' + row.get('amount'));
}

// group by a parent field
for (AggregateResult row : [
    SELECT Account.Name accountName, COUNT(Id) contacts
    FROM Contact
    WHERE AccountId != null
    GROUP BY Account.Name
    ORDER BY Account.Name
]) {
    System.debug(row.get('accountName') + ' has ' + row.get('contacts') + ' contact(s)');
}

Negotiation/Review: 1 deal(s), 250000.0
Closed Won: 2 deal(s), 160000.0
Closed Lost: 1 deal(s), 60000.0
Prospecting: 1 deal(s), 15000.0
Acme Corp has 2 contact(s)
Globex has 1 contact(s)
Initech has 1 contact(s)

HAVING: filtering groups

WHERE filters rows before grouping; HAVING filters the groups afterwards:

SampleData.create();

// accounts whose won business exceeds 50,000
for (AggregateResult row : [
    SELECT AccountId, SUM(Amount) won
    FROM Opportunity
    WHERE StageName = 'Closed Won'
    GROUP BY AccountId
    HAVING SUM(Amount) > 50000
]) {
    Account a = [SELECT Name FROM Account WHERE Id = :(Id) row.get('AccountId')];   // fine for a demo – see below
    System.debug(a.Name + ' won ' + row.get('won'));
}

// countries with more than one account
for (AggregateResult row : [SELECT BillingCountry country, COUNT(Id) n FROM Account GROUP BY BillingCountry HAVING COUNT(Id) > 1]) {
    System.debug(row.get('country') + ': ' + row.get('n') + ' accounts');
}

Globex won 120000.0
India: 3 accounts

The query inside that first loop is acceptable only because a demo returns one group. In real code, collect the AccountIds and query the names once – or group by Account.Name directly, as in the previous example.

COUNT_DISTINCT and grouping by dates

SampleData.create();

AggregateResult d = [SELECT COUNT_DISTINCT(BillingCountry) countries, COUNT_DISTINCT(Industry) industries FROM Account];
System.debug(d.get('countries') + ' countries, ' + d.get('industries') + ' industries');

for (AggregateResult row : [
    SELECT CALENDAR_YEAR(CreatedDate) yr, COUNT(Id) n
    FROM Case
    GROUP BY CALENDAR_YEAR(CreatedDate)
]) {
    System.debug('Cases created in ' + row.get('yr') + ': ' + row.get('n'));
}

2 countries, 4 industries
Cases created in 2026: 3

Date functions include CALENDAR_YEAR, CALENDAR_MONTH, CALENDAR_QUARTER, FISCAL_YEAR, DAY_ONLY (Datetime → Date) and WEEK_IN_YEAR.

Practice

Summary