SOQL GROUP BY: Practical Examples for Salesforce Developers

GROUP BY is one of the easiest ways to move summarization into Salesforce instead of querying thousands of records and aggregating them in application code.

Count Records by a Field

SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry

This returns one aggregate result per Industry rather than one row per Account.

Sum Opportunity Amount by Stage

SELECT StageName, SUM(Amount)
FROM Opportunity
WHERE IsClosed = false
GROUP BY StageName

Multiple Aggregate Functions

SELECT StageName,
       COUNT(Id),
       SUM(Amount),
       AVG(Amount),
       MIN(Amount),
       MAX(Amount)
FROM Opportunity
GROUP BY StageName

Salesforce supports aggregate functions including AVG, COUNT, COUNT_DISTINCT, MIN, MAX, and SUM where applicable.

Group by Multiple Fields

SELECT StageName, LeadSource, COUNT(Id)
FROM Opportunity
GROUP BY StageName, LeadSource

Each unique Stage/Lead Source combination becomes a group.

Group Dates into Useful Buckets

Grouping raw DateTime values is usually less useful than grouping by a date function:

SELECT CALENDAR_YEAR(CloseDate),
       CALENDAR_MONTH(CloseDate),
       SUM(Amount)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)

Other date grouping functions can help with fiscal periods, weeks, quarters, days, and hours depending on the field and use case.

COUNT() vs COUNT(field)

Be deliberate about what you count. Aggregate behavior around nulls differs by function. If the business question is "how many records?", counting Id is often explicit and easy to read:

SELECT Type, COUNT(Id)
FROM Account
GROUP BY Type

Don't Retrieve Rows Just to Count Them

This is usually inferior:

SELECT Id, StageName
FROM Opportunity

followed by an application loop that creates counters. If Salesforce can answer the aggregate question directly, let the query do it and reduce data transfer.