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'));
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);
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...
}
