Aggregate Functions and GROUP BY
By the end of this lesson, you'll be able to:
- Use COUNT, SUM, AVG, MIN, and MAX in a SOQL query
- Group results with GROUP BY and filter groups with HAVING
Prerequisites: Semi-Joins and Anti-Joins
Aggregate functions
COUNT() counts every row; COUNT(fieldName) counts only rows where that field isn't null. SUM, AVG, MIN, and MAX operate on numeric fields. A query using only aggregate functions with no GROUP BY returns a single summary row across the whole result set.
GROUP BY and HAVING
GROUP BY collapses records into one row per unique combination of the grouped field(s). HAVING then filters those grouped rows after aggregation — different from WHERE, which filters individual rows before aggregation happens.
Opportunity totals by stage, filtered on the aggregate
SELECT StageName, COUNT(Id), SUM(Amount)
FROM Opportunity
GROUP BY StageName
HAVING SUM(Amount) > 10000This returns one row per Opportunity stage, with the count and total Amount in that stage — but only for stages whose total exceeds 10,000.
Exercise
Write a query that returns each Account's Id along with the count of its Contacts, for Accounts with more than 5 Contacts.
Show hint
Query Contact, GROUP BY AccountId, and filter the count with HAVING.
Aggregate Functions and GROUP BY — Quick Check
My Notes
Log in to keep private notes on this lesson.
Questions about this lesson
No questions yet — be the first to ask.
Log in to ask a question about this lesson.
Summary
SOQL supports the standard aggregate functions — COUNT, SUM, AVG, MIN, MAX — combined with GROUP BY to summarize records, and HAVING to filter on the aggregated result.