What are SOQL Aggregate Functions?<\/h2>\n\n\n\n
SOQL Aggregate Functions are functions that summarise many rows into one value. The main ones are COUNT, COUNT_DISTINCT, SUM, AVG, MIN and MAX. When you add GROUP BY, you get one summary row per group instead of one for the whole object.<\/p>\n\n\n\n
Think of a class teacher with a marks register. Instead of reading every mark aloud, she reports the class average, the highest mark and the number of students who passed. Aggregate functions give your code those same summary numbers.<\/p>\n\n\n\n
Why SOQL Aggregate Functions Matter<\/h2>\n\n\n\n
Summaries are needed everywhere, from dashboards to validation logic. Here is why SOQL Aggregate Functions matter at Bright Path Tutors.<\/p>\n\n\n\n
- \n
- First, the finance page needs total course fees by level.<\/li>\n\n
- Also, the trainer page shows how many students each course has.<\/li>\n\n
- Next, a class must block new enrolments when a course reaches 30 students.<\/li>\n\n
- Leaders want the average fee of online and offline courses.<\/li>\n\n
- Finally, adding values in Apex loops wastes CPU time and heap.<\/li>\n<\/ul>\n\n\n\n
So aggregates are faster, shorter and lighter than looping over records.<\/p>\n\n\n\n
How SOQL Aggregate Functions Work<\/h2>\n\n\n\n
SOQL Aggregate Functions follow a clear order. First, the WHERE clause filters records. Next, GROUP BY splits the remaining records into groups. Then each aggregate function calculates a value per group. After that, HAVING filters the groups. Finally, Apex receives a list of AggregateResult rows.<\/p>\n\n\n\n
![\"SOQL](\"https:\/\/swapnilzambare.com\/blog\/wp-content\/uploads\/2026\/11\/soql-aggregate-functions-group-by-diagram-1-1024x640.png\")
How an aggregate query turns records into group totals.<\/figcaption><\/figure>\n\n\n\n COUNT and COUNT_DISTINCT<\/h3>\n\n\n\n
COUNT()<\/code> returns the number of rows as an Integer. In contrast,COUNT(field)<\/code> counts rows where the field is not blank. Finally,COUNT_DISTINCT(field)<\/code> counts unique values.<\/p>\n\n\n\nSUM and AVG<\/h3>\n\n\n\n
For money,
SUM(Fee__c)<\/code> adds values, andAVG(Fee__c)<\/code> returns the average. They work on number, currency and percent fields.<\/p>\n\n\n\nMIN and MAX<\/h3>\n\n\n\n
MIN<\/code> andMAX<\/code> return the smallest and largest values. They also work on dates, such as the earliest start date.<\/p>\n\n\n\nGROUP BY and HAVING<\/h3>\n\n\n\n
GROUP BY creates one row per value, such as per level. HAVING filters those groups, much like WHERE filters records.<\/p>\n\n\n\n
AggregateResult<\/h3>\n\n\n\n
Grouped results come back as
AggregateResult<\/code> rows. You read values withget('alias')<\/code>, then cast them, becauseget<\/code> returns a generic Object.<\/p>\n\n\n\nSOQL Aggregate Functions at a Glance<\/h2>\n\n\n\n
The table below lists each function and what it returns.<\/p>\n\n\n\n
Function<\/th> Example<\/th> Returns<\/th><\/tr><\/thead> COUNT()<\/td> SELECT COUNT() FROM Course__c<\/td> Integer count of rows<\/td><\/tr> COUNT(field)<\/td> COUNT(Fee__c)<\/td> Rows where field is filled<\/td><\/tr> COUNT_DISTINCT(field)<\/td> COUNT_DISTINCT(Level__c)<\/td> Number of unique values<\/td><\/tr> SUM(field)<\/td> SUM(Fee__c) totalFee<\/td> Total of the values<\/td><\/tr> AVG(field)<\/td> AVG(Fee__c) avgFee<\/td> Average value<\/td><\/tr> MIN and MAX<\/td> MIN(Start_Date__c)<\/td> Smallest or largest value<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n WHERE vs HAVING<\/h2>\n\n\n\n
The table below compares the two filters in aggregate queries.<\/p>\n\n\n\n
Feature<\/th> WHERE<\/th> HAVING<\/th><\/tr><\/thead> Filters<\/td> Individual records<\/td> Groups<\/td><\/tr> Runs<\/td> Before grouping<\/td> After grouping<\/td><\/tr> Can use aggregate functions<\/td> No<\/td> Yes<\/td><\/tr> Example<\/td> WHERE Is_Online__c = true<\/td> HAVING COUNT(Id) > 10<\/td><\/tr> Needs GROUP BY<\/td> No<\/td> Yes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n When to use which is simple. Use WHERE to drop records before grouping. Use HAVING to drop groups after the totals are calculated.<\/p>\n\n\n\n
![\"SOQL](\"https:\/\/swapnilzambare.com\/blog\/wp-content\/uploads\/2026\/11\/soql-aggregate-functions-group-by-diagram-2-1024x640.png\")
WHERE vs HAVING.<\/figcaption><\/figure>\n\n\n\n A Real SOQL Aggregate Functions Example in Apex<\/h2>\n\n\n\n
Let’s build a small reporting service for Bright Path Tutors.<\/p>\n\n\n\n
Step 1: Count All Courses<\/h3>\n\n\n\n
Integer totalCourses = [SELECT COUNT() FROM Course__c];\nSystem.debug('Total courses: ' + totalCourses);<\/code><\/pre>\n\n\n\nCOUNT()<\/code> with no field returns a plain Integer. So you assign it straight to a number, with no list involved.<\/p>\n\n\n\nStep 2: Total and Average Fee by Level<\/h3>\n\n\n\n
List<AggregateResult> rows = [\n SELECT Level__c level, COUNT(Id) courses,\n SUM(Fee__c) totalFee, AVG(Fee__c) avgFee\n FROM Course__c\n WHERE Is_Online__c = true\n GROUP BY Level__c\n ORDER BY SUM(Fee__c) DESC\n];\n\nfor (AggregateResult ar : rows) {\n String level = (String) ar.get('level');\n Integer courses = (Integer) ar.get('courses');\n Decimal total = (Decimal) ar.get('totalFee');\n Decimal avg = (Decimal) ar.get('avgFee');\n System.debug(level + ': ' + courses + ' courses, total ' + total + ', average ' + avg.setScale(0));\n}<\/code><\/pre>\n\n\n\nEach function has an alias, such as
totalFee<\/code>, which makesget<\/code> easy to read. GROUP BY returns one row per level, and ORDER BY sorts levels by total fee. Inside the loop, we cast each value to the right type.<\/p>\n\n\n\nStep 3: Read the Output<\/h3>\n\n\n\n
Here is an example of the debug log for Step 2.<\/p>\n\n\n\n
USER_DEBUG [13]|DEBUG|Intermediate: 6 courses, total 34800, average 5800\nUSER_DEBUG [13]|DEBUG|Beginner: 8 courses, total 33600, average 4200\nUSER_DEBUG [13]|DEBUG|Advanced: 3 courses, total 24000, average 8000<\/code><\/pre>\n\n\n\nStep 4: Find Full Courses with HAVING<\/h3>\n\n\n\n
List<AggregateResult> fullCourses = [\n SELECT Course__c courseId, COUNT(Id) students\n FROM Enrolment__c\n WHERE Status__c = 'Active'\n GROUP BY Course__c\n HAVING COUNT(Id) >= 30\n];\nSystem.debug('Full courses: ' + fullCourses.size());<\/code><\/pre>\n\n\n\nWHERE keeps only active enrolments. GROUP BY counts students per course, and HAVING keeps only courses with 30 or more. A trigger could use this result to block new enrolments.<\/p>\n\n\n\n
Step 5: Group by Month<\/h3>\n\n\n\n
for (AggregateResult ar : [\n SELECT CALENDAR_MONTH(Start_Date__c) month, COUNT(Id) courses\n FROM Course__c\n WHERE Start_Date__c = THIS_YEAR\n GROUP BY CALENDAR_MONTH(Start_Date__c)\n ]) {\n System.debug('Month ' + ar.get('month') + ': ' + ar.get('courses'));\n}<\/code><\/pre>\n\n\n\nDate functions such as CALENDAR_MONTH group records by month. This is ideal for simple charts in a Lightning component.<\/p>\n\n\n\n
SOQL Aggregate Functions and Limits<\/h2>\n\n\n\n
SOQL Aggregate Functions run as one SOQL query. However, most aggregate functions count every record they process toward the 50,000 row limit, even though only a few rows come back. So filter with WHERE before you group, especially on large objects.<\/p>\n\n\n\n
Common Mistakes with SOQL Aggregate Functions<\/h2>\n\n\n\n
- \n
- Mixing COUNT() with other fields, which is not allowed.<\/li>\n\n
- Selecting a field that is not in GROUP BY or inside a function.<\/li>\n\n
- Forgetting aliases, so values come back as expr0 and expr1.<\/li>\n\n
- Casting a SUM result to Integer instead of Decimal.<\/li>\n\n
- Using WHERE with an aggregate function instead of HAVING.<\/li>\n<\/ul>\n\n\n\n
Best Practices for SOQL Aggregate Functions<\/h2>\n\n\n\n
- \n
- Give every aggregate an alias that describes the value.<\/li>\n\n
- Filter with WHERE first, then group.<\/li>\n\n
- Use HAVING only for conditions on totals.<\/li>\n\n
- Cast results carefully: Decimal for SUM and AVG, Integer for COUNT.<\/li>\n\n
- Prefer aggregates over Apex loops that add values.<\/li>\n<\/ul>\n\n\n\n
Practice Task<\/h2>\n\n\n\n
Write a query that shows the number of active enrolments per student, but only for students in more than two courses. Hint: group Enrolment__c by Student__c and use HAVING COUNT(Id) > 2.<\/p>\n\n\n\n
Most Asked Interview Questions on SOQL Aggregate Functions<\/h2>\n\n\n\n
SOQL Aggregate Functions are a favourite interview topic, because they test both SOQL and Apex skills. Here are the 20 questions asked most.<\/p>\n\n\n\n
Basic Questions on SOQL Aggregate Functions<\/h3>\n\n\n\n
Q1: What are aggregate functions in SOQL?<\/strong><\/p>\n\n\n\n
They summarise many rows into one value. The main ones are COUNT, COUNT_DISTINCT, SUM, AVG, MIN and MAX.<\/p>\n\n\n\n
Q2: What does COUNT() return?<\/strong><\/p>\n\n\n\n
It returns an Integer with the number of rows, so you assign it to a number, not a list.<\/p>\n\n\n\n
Q3: What is the difference between COUNT() and COUNT(field)?<\/strong><\/p>\n\n\n\n
COUNT() counts all rows. In contrast, COUNT(field) counts only rows where that field is not blank.<\/p>\n\n\n\n
Q4: What is AggregateResult?<\/strong><\/p>\n\n\n\n
It is the Apex type for rows returned by grouped or aggregate queries. You read values with get().<\/p>\n\n\n\n
Q5: What is an alias in an aggregate query?<\/strong><\/p>\n\n\n\n
It is a name after the function, such as SUM(Fee__c) totalFee. Then you read it with ar.get(‘totalFee’).<\/p>\n\n\n\n
Q6: What happens if you do not use an alias?<\/strong><\/p>\n\n\n\n
Salesforce names values expr0, expr1 and so on, which makes code harder to read.<\/p>\n\n\n\n
Q7: What does GROUP BY do?<\/strong><\/p>\n\n\n\n
It creates one result row for each unique value of the grouped field, such as one row per level.<\/p>\n\n\n\n
Code and Scenario Questions on SOQL Aggregate Functions<\/h3>\n\n\n\n
Q8: What is the difference between WHERE and HAVING?<\/strong><\/p>\n\n\n\n
WHERE filters records before grouping. HAVING, however, filters groups after the totals are calculated.<\/p>\n\n\n\n
Q9: How do you find courses with more than 30 students?<\/strong><\/p>\n\n\n\n
Group enrolments by course and add HAVING COUNT(Id) > 30.<\/p>\n\n\n\n
Q10: Which type do you cast SUM and AVG results to?<\/strong><\/p>\n\n\n\n
Usually Decimal, because they can return decimal values.<\/p>\n\n\n\n
Q11: Can you use COUNT() with other fields in the SELECT?<\/strong><\/p>\n\n\n\n
No. COUNT() must be used alone. Instead, use COUNT(Id) with other fields and GROUP BY.<\/p>\n\n\n\n
Q12: How do you group records by month?<\/strong><\/p>\n\n\n\n
Use a date function, such as GROUP BY CALENDAR_MONTH(Start_Date__c).<\/p>\n\n\n\n
Q13: Can you ORDER BY an aggregate value?<\/strong><\/p>\n\n\n\n
Yes. For example, ORDER BY SUM(Fee__c) DESC sorts groups by total.<\/p>\n\n\n\n
Q14: How do you count unique values?<\/strong><\/p>\n\n\n\n
Use COUNT_DISTINCT(field), for example COUNT_DISTINCT(Level__c).<\/p>\n\n\n\n
Advanced Questions on SOQL Aggregate Functions<\/h3>\n\n\n\n
Q15: Do aggregate queries help with governor limits?<\/strong><\/p>\n\n\n\n
They use one query, and they save CPU and heap. However, most functions still count processed records toward the 50,000 row limit.<\/p>\n\n\n\n
Q16: What is GROUP BY ROLLUP?<\/strong><\/p>\n\n\n\n
It adds subtotal rows for each group level and a grand total, similar to report subtotals.<\/p>\n\n\n\n
Q17: Can you use aggregate functions in a SOQL for loop?<\/strong><\/p>\n\n\n\n
Yes. A for loop over an aggregate query is a clean way to process grouped rows.<\/p>\n\n\n\n
Q18: Why might an aggregate query be slow on large objects?<\/strong><\/p>\n\n\n\n
It may scan many records. So add selective WHERE filters on indexed fields before grouping.<\/p>\n\n\n\n
Q19: Can you group by a relationship field?<\/strong><\/p>\n\n\n\n
Yes. For example, you can group enrolments by Course__c, then read course names in another query or with a relationship field in the group.<\/p>\n\n\n\n
Q20: When would you use Apex loops instead of aggregates?<\/strong><\/p>\n\n\n\n
Only when the calculation is too complex for SOQL, such as custom business rules per record. Otherwise, aggregates are faster and simpler.<\/p>\n\n\n\n
Official Resources<\/h2>\n\n\n\n