Let’s look a bit on the ability to aggregate values in Apex using some useful methods and Queries.

• This post will review the Dynamic query implementation for using SOQL Aggregate Functions

The Dynamic Method Signature

Expecting the following parameters:

• sObjectName – The sObject to aggreagate
• groupByField – The group By field (need to be isGroupable() )
• aggField – The field that will be aggregated and sorted
• operation – This can be a default one or can run all operation on a single query.
• queryLimit – The limit for the query.

@AuraEnabled
public static String aggregateMethod(String sObjectName, String groupByField, String aggField, String operation, Integer queryLimit ) {
  ...
}

The Query String

String queryGroupByField = 'SELECT ' + groupByField+ ',';
       queryGroupByField += ' ' + operation+'('+aggField+')gvar';         
       // the below will work only if field type is Number
       // queryGroupByField += ' ,SUM'+'('+aggField+')gsum,';
       // queryGroupByField += ' MAX'+'('+aggField+')gmax,';
       // queryGroupByField += ' AVG'+'('+aggField+')gavg,';
       // queryGroupByField += ' MIN'+'('+aggField+')gmin';
     queryGroupByField += ' FROM ' + sObjectName;
     queryGroupByField += ' Group By ' + groupByField;
     queryGroupByField += ' ORDER BY ' + operation+'('+aggField+') ' + sortOrder;
     queryGroupByField += ' LIMIT ' + queryLimit;

Executing the Query

 List<AggregateResult> aggList=new List<AggregateResult>();
     aggList = doQuery(queryGroupByField, 'queryGroupByField');

Handle results

List<Map<String,Object>> dataList = new List<Map<String,Object>> (); 
for(AggregateResult res:aggList) {
     Map<String,Object> nodes = new Map<String,Object> {
                    'groupByField'=>groupByField,
                    'groupByFieldLabel'=>fieldLabel,
                    'groupByValue' => res.get(groupByField),
                    'countValue' => res.get('gcount'),
                    'operation' => operation,
                    'sObject' => sObjectName
                };
          // depends on which variable you are using in query for operation
          // nodes.put('sumValue',res.get('gsum'));
          // nodes.put('avgValue',res.get('gavg'));
          // nodes.put('minValue',res.get('gmin'));
          // nodes.put('maxValue',res.get('gmax'));
    dataList.add(nodes);
}

Resources:

• AggregateResult
• Working with SOQL Aggregate Functions