Aggregate SOQL, Handy Stuff

As most people are aware, one of the big features included in the API 18.0 release for Salesforce was aggregate functions for SOQL queries. Essentially these boil down to those functions that most people are used to having in SQL, i.e. you can do things like count a number of rows which fulfill a particular criteria, or sum the fields of rows. They come along with the very handy GROUP BY clause which allows you to roll up your data rather nicely.

So for example, if I have a Transaction__c object which includes information such as a customer's ID (Cust_ID__c) and an amount paid (let's go wild and call it Amount__c), we could find the total spent for each customer by using SUM on the Amount__c field and group the results according to the customer IDs.

select Cust_ID__c, SUM(Amount__c) from Transaction__c group by Cust_ID__c

Something to note here is that you have to group by all fields which aren't used in an aggregate function, so maybe if a customer could have multiple accounts and there was another field Account_Number__c which we'd want to select also, we'd have to group by the customer ID and then account number field.

A major difference between queries which use aggregate functions and those that don't is that while a regular query returns records of the type queried (i.e. grabbing fields from Opportunity will return a list of Opportunity objects), aggregate queries return collections of AggregateResult objects. This means that you can't put results into a collection of Transaction__c objects for example, but apart from that the only real difference is that you can't simply grab field values using the .fieldname notation. Instead you have to use .get() passing the field name as a string.

for(AggregateResult ar : listResults)
{
    mapCustIDToTotal.put(ar.get('Cust_ID__c'), ar.get('expr0'));
}

The ugly looking 'expr0' simply means expression 0, meaning the 0th expression in the query which in this case happens to also be the only one, the SUM of the Amount__c field; if we had another field called Refunded__c which we also used with SUM immediately after the amount then that would be referred to by 'expr1'. Using aliases (names) for the fields as you can do with any other query makes this all a little neater.

double dTotalForAll = 0;

for(AggregateResult ar : [select Cust_ID__c, SUM(Amount__c) TotalAmount
    from Transaction__c group by Cust_ID__c])
{
    dTotalForAll += ar.get('TotalAmount');
}

Obviously this is a slightly contrived example but hopefully you get the picture!