What is Aggregate Functions in SOQL?

What is  Aggregate Functions in SOQL?

Aggregate functions in SOQL, such as SUM() and MAX(), allow you to roll up and summarize your data in a query. 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)aver FROM Opportunity];
Object avgAmount = groupedResults[0].get('aver');

So the functions count(fieldname), count_distinct(), sum(), avg(), min() and max() return an AggregateResult object (if one row is returned from the query) or a List of AggregateResult objects (if multiple rows are returned from the query). You access values in the AggregateResult object much like a map calling a “get” method with the name of the column. In the example below you can see how you can access a column name (leadsource), alias (total) and an unnamed column (expr0). If you have multiple unnamed columns you reference in the order called with expr0, expr1, expr2, etc. 

List<aggregateResult> results = [select leadsource, count(name) total,

 count(state) from lead group by leadsource ]; 

for (AggregateResult ar : results)

 System.debug(ar.get('leadsource')+'-'+ar.get('total')+'-'+ar.get('expr0')); 

The AggregateResult returns the value as an object. So for some operations you will need to cast they value to assign them appropriately.

Set<id> accountIds = new Set<id>();
for (AggregateResult results : [select accountId from contact group by accountId])
  accountIds.add((ID)results.get('accountId'));

One this to be aware of is that the count() function does not return an AggregateResult object. The resulting query result size field returns the number of rows: 

Integer rows = [select count() from contact];
System.debug('rows: ' + rows);

You can also do some cool things like embed the SOQL right in your expression:

if ([select count() from contact where email = null] > 0) {
  // do some sort of processing...
}

 

Posted in: Aggregate functions in SOQL,apex,Salesforce,Salesforce examples,salesforce training,sfdc,srini,Visualforce examples,Visualforce Training