In Spring ‘10, 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) {

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]) {

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 and running your code there.