Apex Intermediate Progress | ( Lessons)

Lesson: 08 | AggregateResult

Notes

Aggregate functions in salesforce include AVG(), COUNT(), MIN(), MAX(), SUM(). The functions like SUM() and MAX() in SOQL allow to roll up and summarize the data in a query. The GROUP BY clause in a SOQL query is to avoid iterating through individual query results and used to specify a group of records instead of processing many individual records.

AVG() – Returns the average value of a numeric field
COUNT() – Returns the number of rows matching the query criteria
MIN() – Returns the minimum value of a field
MAX() – Returns the maximum value of a field
SUM() – Returns the total sum of a numeric field

You can use aggregate functions without using a GROUP BY clause.

For example, you could use the AVG() aggregate function to find the average Amount for all your opportunities.

AggregateResult[] groupedResults = [SELECT AVG(Amount)AV FROM Opportunity];

Object avgAmount = groupedResults[0].get('AV');

System.debug(avgAmount);

AggregateResult[] groupedResults = [SELECT AVG(Amount) FROM Opportunity];

Object avgAmount = groupedResults[0].get('expr0');

System.debug(avgAmount);

Aggregate functions become a more powerful tool to generate reports when you use them with a GROUP BY clause.

For example, you could find the average Amount for all your opportunities by Account.

List<AggregateResult> myacc =[SELECT  StageName , MIN(Amount) FROM Opportunity GROUP BY StageName ];

for (AggregateResult x:myacc ){
    
    System.debug(x);
}

In the following example, we updated the Annual Revenue of each Account with sum of the Amounts of all the related Opportunities

List<Account> acList = [SELECT Id, AnnualRevenue FROM Account ];

for(Account ac : acList){
    
    AggregateResult[] aggList= [SELECT AccountId, Sum(Amount) FROM Opportunity WHERE AccountId =: ac.Id  GROUP BY AccountId ];
    for (AggregateResult agg : aggList) {
        ac.put('AnnualRevenue', agg.get('expr0'));
    }
}
update acList;

You can also use casting here.

List<Account> acList = [SELECT Id, AnnualRevenue FROM Account ];

for(Account ac : acList){
    
    AggregateResult[] aggList= [SELECT AccountId, Sum(Amount) FROM Opportunity WHERE AccountId =: ac.Id  GROUP BY AccountId ];
    for (AggregateResult agg : aggList) {
        Decimal sumAmmount = (Decimal) agg.get('expr0');
        
        ac.AnnualRevenue=sumAmmount;
    }
}
update acList;

Exercises

#	Exercise
1	Average Annual Revenue by Account Rating

Prev You need to login to have access to the quizzes.

Mastered by 84 users.

Apex Intermediate

A. Apex DML Operations (8 lessons)

B. Apex Classes (17 lessons)

C. Apex Triggers (9 lessons)

D. Apex Exceptions (11 lessons)

E. Asynchronous Apex (6 lessons)

F. SOSL (10 lessons)

G. Testing Apex (9 lessons)

H. Apex Fast Recap (142 lessons)

I. Build Apex Coding Skills (64 lessons)

J. Miscellaneous (17 lessons)

You need to complete all the quizzes before certification.

Go to top

Login to your account

Lesson: 08 | AggregateResult

Notes

Apex Intermediate