Aggregates, GROUP BY & Date Literals

Aggregate functions

SELECT COUNT() FROM Case WHERE Status != 'Closed'

Integer openCases = [SELECT COUNT() FROM Case WHERE Status != 'Closed'];

GROUP BY and HAVING

WHERE filters rows before grouping, HAVING filters groups after aggregation.

SELECT Industry, SUM(AnnualRevenue) revenue, COUNT(Id) accounts
FROM Account
WHERE Industry != null
GROUP BY Industry
HAVING SUM(AnnualRevenue) > 5000000
ORDER BY SUM(AnnualRevenue) DESC
LIMIT 3

Rules:

• Every non-aggregated field in SELECT must be in GROUP BY → otherwise "Field must be grouped or aggregated".
• Aliases (revenue) only work on aggregate expressions; unaliased ones are named expr0, expr1, …
• You can ORDER BY an aggregate.

AggregateResult in Apex

Map<Id, Integer> contactsPerAccount = new Map<Id, Integer>();
for (AggregateResult ar : [
    SELECT AccountId acc, COUNT(Id) n
    FROM Contact
    WHERE AccountId IN :accountIds
    GROUP BY AccountId
]) {
    contactsPerAccount.put((Id) ar.get('acc'), (Integer) ar.get('n'));
}

This is the standard roll-up on a lookup pattern used in triggers (a roll-up summary field only works on master-detail).

Date literals

Relative to the running user's time zone — no hard-coded dates.

SELECT Name, Amount, CloseDate
FROM Opportunity
WHERE IsClosed = false
  AND Amount >= 50000
  AND CloseDate = THIS_QUARTER
ORDER BY CloseDate, Name

Date vs Datetime literals: CloseDate = 2026-06-30 (Date field) vs CreatedDate > 2026-06-30T00:00:00Z (Datetime field).

Interview questions

1. Difference between WHERE and HAVING? → Row filter vs group filter.
2. How do you count child records per parent without a roll-up summary? → Aggregate SOQL grouped by the lookup field, then update parents.
3. What does COUNT() return vs COUNT(Id)? → COUNT() returns an Integer total; COUNT(Id) returns AggregateResult rows (usable with GROUP BY).