Aggregate Queries

Aggregate functions summarize data.

Common functions:
● COUNT()
● SUM()
● AVG()
● MIN()
● MAX()

Example – Count Total Accounts

Input:

Total Account records = 5

Code:

AggregateResult result =
    [SELECT COUNT(Id) total FROM Account];

Integer total = (Integer) result.get('total');
System.debug(total);

Output:

Explanation

● COUNT() returns number of records

● AggregateResult stores summary data

Example – Sum Opportunity Amount

Input:

Opportunity Amounts = 1000, 2000, 500

Code:

AggregateResult result =
    [SELECT SUM(Amount) totalAmount FROM Opportunity];

Decimal total = (Decimal) result.get('totalAmount');
System.debug(total);

Output:

Explanation

● SUM() calculates total value

● Alias is used to retrieve result

GROUP BY Queries

Used to group records.

Example – Count Accounts by Industry

Input:

Industry: IT → 3 accounts
Industry: Finance → 2 accounts

Code:

List<AggregateResult> results =
    [SELECT Industry, COUNT(Id) total
     FROM Account
     GROUP BY Industry];

for(AggregateResult ar : results) {
    System.debug(ar.get('Industry') + ' → ' + ar.get('total'));
}

Output:

IT → 3
Finance → 2

Explanation

● Records grouped by Industry

● COUNT calculated per group

Child Relationship Queries

Fetch child records along with parent.

Example – Fetch Contacts Under Each Account

Input:

Account: MSD Automations
Contacts: Divyesh, Pankaj

Code:

List<Account> accounts =
[
    SELECT Name,
           (SELECT Name FROM Contacts)
    FROM Account
];

for(Account acc : accounts) {
    System.debug('Account: ' + acc.Name);
    for(Contact con : acc.Contacts) {
        System.debug(' - ' + con.Name);
    }
}

Output:

Account: MSD Automations
 - Divyesh
 - Pankaj

Explanation

● Subquery fetches child records

● No extra SOQL required

IN Clause Pattern

Used when filtering using multiple values.

Example – Fetch Accounts by Multiple Ids

Input:

accountIds = [Id1, Id2]

Code:

Set<Id> accountIds = new Set<Id>{Id1, Id2};

List<Account> accounts =
    [SELECT Name FROM Account WHERE Id IN :accountIds];

Output:

Matching account records returned

Explanation

● IN clause accepts collection

● Efficient filtering pattern

Query Selectivity & Indexing (Concept)

Highly selective queries run faster.

Good filters:
● Id
● External Id
● Indexed fields
● Selective picklists

Avoid filtering on:
● Long text fields
● Formula fields
● Non-selective fields

Query Limits Awareness

Important limits:
● Max 100 SOQL queries per transaction
● Max 50,000 records returned

Always bulkify queries.

Common Pitfalls

● Using SELECT * (not allowed)

● Missing filters on large objects
● Nested queries without limits
● Ignoring selectivity

Productivity Tips

● Use Query Plan tool in Setup

● Test queries in Developer Console
● Log record counts
● Use LIMIT during development

Conclusion

You now understand:
● Aggregate queries

● GROUP BY usage
● Child relationship queries
● Performance considerations
● Bulk-safe query patterns

Happy Learning! 📘

Apex Beginner to Advance

Advanced SOQL in Apex