SOQL Aggregate Functions

SOQL aggregate functions are extremely useful for summarizing sobject data in a helpful manner to end user business requirements.In this post I am discussing how you can use aggregate functions on your SOQL queries and in Apex your code.Before we move on  with the discussion first get familiar with available Aggregate Functions .


The SOQL queries which uses aggregate functions have few differences from typical soql queries without them.

• If you are using aggregate functions in your query  you can only use aggregate function(s) and group by fields in SELECT statement.
• SOQL will return a list of type aggregateresult  instead of sobject type of queried object.

example queries

 Integer recordCount = [Select count() from MyObject__c];  

in the case you use count() without group by  you will be returned a integer in other cases list of aggregateresult. For more details refer Count() vs Count(fieldname)

 AggregateResult[] aggr = [Select Category__c,count(Id) From MyObject__c Group By Category__c];  
 for(AggregateResult ar :aggr )  
 {  
   system.debug('count :- '+ar.get('expr0') +' category'+ar.get('Category__c '));  
 }  

when you use group by clause with a aggregate function you will get a aggregateresult type list. Size of this list will depend  the number of distinct values in queried object's group by field(s)

If you want to assign the returned result values to variables they have to be cast to the relevant type.

 eg:- integer count =(Integer) ar.get('expr0');  

Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri, where i denotes the order of the aggregated fields with no explicit aliases. The value of i starts at 0 and increments for every aggregated field with no explicit alias.