Using AggregateResult in Salesforce.com SOQL
April 12th, 2010
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.
1 2 3 4 | 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.
1 2 3 | 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:
1 2 | 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:
1 2 3 | 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.
Categories: Apex, Code Sample, Salesforce
Yeah it’s a bit of an advanced syntax for people just picking up apex, excellent post.
I originally thought this was fixed, but it’s not… Add a limit 10000 to your aggregate function queries because there is a hard limit if your query is examining > 10000 records.
@Scott
Ya, this feature is cool but it is still pretty limited with the 10000 row limit. One thing I have seen is that adding the limit 10000 doesn’t always save you. For example:
List results = [select leadsource, count(name) total,
count(state) from lead group by leadsource limit 1];
Can still fail and throw and uncatchable exception if you have one leadsource with more than 10000 rows.
I tried with max() function and I cant save the resultant value in an Integer variable.
AggregateResult result = [select max(autonumber__c) from Account where Country = 'ABCD' ];
Integer res = Integer.ValueOf(inte.get(‘expr0′);
Its giving me the exception of Invalid Integer. The result is an object. Can you tell me where am I going wrong? I am sure it’d be a small thing.
Thanks
it worked…Integer.ValueOf(String.ValueOf(inte.get(‘expr0′)))
strange