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 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 accountIds = new Set(); 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.