Working with SOQL Aggregate Functions

Aggregate functions in SOQL, such as SUM() and MAX(), allow you to roll up and summarize your data in a query. For more information on aggregate functions, see ”Aggregate Functions” in the Salesforce SOQL and SOSL Reference Guide.

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.

1swfobject.registerObject("clippy.codeblock-0", "9");AggregateResult[] groupedResults
2  = [SELECT AVG(Amount)aver FROM Opportunity];
3Object avgAmount = groupedResults[0].get('aver');

Note that any query that includes an aggregate function returns its results in an array of AggregateResult objects. AggregateResult is a read-only sObject and is only used for query results.

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 campaign.

1swfobject.registerObject("clippy.codeblock-1", "9");AggregateResult[] groupedResults
2  = [SELECT CampaignId, AVG(Amount)
3      FROM Opportunity
4      GROUP BY CampaignId];
5for (AggregateResult ar : groupedResults)  {
6    System.debug('Campaign ID' + ar.get('CampaignId'));
7    System.debug('Average amount' + ar.get('expr0'));
8}

Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri, where i denotes the order of the aggregated fields with no explicit aliases. The value of i starts at 0 and increments for every aggregated field with no explicit alias. For more information, see ”Using Aliases with GROUP BY” in the Salesforce SOQL and SOSL Reference Guide.