SOQL GROUP BY with HAVING: Filter Aggregate Results

A common SOQL aggregation question is: should this condition go in WHERE or HAVING?

The practical distinction is simple:

• WHERE filters records before grouping.
• HAVING filters the grouped/aggregate results.

Filter Input Records with WHERE

SELECT StageName, COUNT(Id)
FROM Opportunity
WHERE CreatedDate = THIS_YEAR
GROUP BY StageName

Only Opportunities created this year participate in the groups.

Filter Groups with HAVING

Suppose you only want stages containing more than 100 Opportunities:

SELECT StageName, COUNT(Id)
FROM Opportunity
GROUP BY StageName
HAVING COUNT(Id) > 100

The count must be calculated before Salesforce can decide which groups qualify.

WHERE and HAVING Together

SELECT AccountId, SUM(Amount)
FROM Opportunity
WHERE IsClosed = false
  AND AccountId != null
GROUP BY AccountId
HAVING SUM(Amount) >= 100000

Read it in two steps:

1. Consider only open Opportunities with an Account.
2. Return only Accounts whose grouped open pipeline is at least 100,000.

Filter on More Than One Aggregate

SELECT AccountId, COUNT(Id), SUM(Amount)
FROM Opportunity
WHERE AccountId != null
GROUP BY AccountId
HAVING COUNT(Id) >= 3
   AND SUM(Amount) >= 250000

A Useful Mental Model

If your condition makes sense for an individual Salesforce record, it probably belongs in WHERE.

If the condition only makes sense after records have been counted, summed, averaged, or grouped, it belongs in HAVING.

Pushing valid record-level filters into WHERE can also reduce the amount of data that must participate in aggregation.

References

• Salesforce Developers: Group Aggregate Results
• Salesforce Developers: Aggregate Examples