In Spring '10, Salesforce.com released new Apex functionality for aggregate functions in SOQL. These queries return an AggregateResult object which can be somewhat confusing at first. I've noticed quite a people searching my blog for this topic so I thought I'd throw some examples together.
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...
}
I run across this error once in awhile: Invalid type: AggregateResult. The error happens when you execute the code in a tool that is not using the 18 API. This happens to me frequently when I execute code anonymously in Eclipse. Try opening the System Log in Salesforce.com and running your code there.
