Search code examples
salesforceapexapex-code

Multiple AggregateResult Querys

Hi guys,
I'm currently trying to join two objects in a same query or result.
My question is if it's possible to show or debug the sum of FIELD A FROM LEAD + sum of FIELD B FROM two different Objects. Here's an example I'm working on:
Btw I really appreciate your time and comments, and if i'm making a mistake pls let me know, thank you. 

public static void example() {

   String sQueryOne;
   String sQueryTwo;
   AggregateResult[] objOne;
   AggregateResult[] objTwo;

   //I tried to save the following querys into a sObject List
   List<SObject> bothObjects = new List<SObject>();

   sQueryOne = 'Select Count(Id) records, Sum(FieldA) fieldNA From Lead';
   objOne = Database.query(sQueryOne);
   sQueryTwo = 'Select Count(Id) records, Sum(FieldA) fieldNB From Opportunity';
   objTwo = Database.query(sQueryTwo);

   bothObjects.addAll(objOne);
   bothObjects.addAll(objTwo);

   for(sObject totalRec : bothObjects) {
       //There's a Wrapper(className) I created which contains some variables(totalSum)
       className finalRes = new className();
       finalRes.totalSum = (Integer.valueOf(fieldNA)) + (Integer.valueOf(fieldNB));
       System.debug('The sum is: '+finalRes.totalSum);

For example if I call a System debug with the previous variable finalRes.totalSum it's just showing the first value(fieldNA) duplicated.

The following debug shows the current values of the sObject List which I want to sum for example FIELD0 = from leads, FIELD0 = from Opportunities.

Debug bothObjects variable

   }
}
Solution

  • You access the columns in AggregateResult by calling get('columnAlias'). If you didn't specify an alias they'll be autonumbered by SF as expr0, expr1... When in doubt you can always go System.debug(results);

    Some more info: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

    This might give you some ideas:

    List<AggregateResult> results = new List<AggregateResult>{
    [SELECT COUNT(Id) records, SUM(NumberOfEmployees) fieldA, SUM(AnnualRevenue) fieldB FROM Account],
    [SELECT COUNT(Id) records, SUM(Amount) fieldA, SUM(TotalOpportunityQuantity) fieldB FROM Opportunity],
    [SELECT COUNT(Id) records, SUM(NumberOfEmployees) fieldA, SUM(AnnualRevenue) fieldB FROM Lead]
    /* hey, not my fault these are only 2 standard numeric fields on Lead. 
    It doesn't matter that they're identical to Account fields, what matters is what their SUM(...) aliases are
    */
};

List<Decimal> totals = new List<Decimal>{0,0,0};

for(AggregateResult ar : results){
    totals[0] += (Decimal) ar.get('records');
    totals[1] += (Decimal) ar.get('fieldA');
    totals[2] += (Decimal) ar.get('fieldB');
}

System.debug(totals); // (636, 8875206.0, 9819762558.0) in my dev org

    (I'm not saying it's perfect, your wrapper class sounds like better idea or maybe even Map<String, Decimal>. Depends what are you going to do with the results)