+ Start a Discussion
Jason CiemiegaJason Ciemiega 

Multiple query aggregateResult

  • I am trying to find my way to wrap my head around this problem.  I am trying to eliminate 3 of these for AggregateResult loops and just make one since it is getting the same object.  I am a new Salesforce Developer and I am trying to figure out what will be the most efficient way to do this.
  • If anyone has an input on this I'd definitely love to hear it.

AggregateResult[] asgns = [SELECT LP_SAS__c, SUM(Exposure_Amt__c)total FROM CC_ASGN__c WHERE LP_SAS__c IN :sasIds AND Status__c = 'Assigned' GROUP by LP_SAS__c]; 

AggregateResult[] asgnsres = [SELECT LP_SAS__c, SUM(Reserve_Amount__c)total, SUM(Discount_Unearned__c)total1, SUM(Exposure_Amt__c)total2 FROM CC_ASGN__c WHERE LP_SAS__c IN :sasIds GROUP by LP_SAS__c];

AggregateResult[] asgnsbb = [SELECT LP_SAS__c, SUM(Amount_Deducted_from_Reserve__c)total, SUM(Buyback_DE__c)total1, SUM(Exposure_Amt__c)total2 FROM CC_ASGN__c WHERE LP_SAS__c IN :sasIds AND Status__c = 'Buyback' GROUP by LP_SAS__c];

AggregateResult[] asgnswo = [SELECT LP_SAS__c, SUM(Amount_Deducted_from_Reserve__c)total, SUM(Buyback_DE__c)total1, SUM(Exposure_Amt__c)total2 FROM CC_ASGN__c WHERE LP_SAS__c IN :sasIds AND Status__c = 'Write-off' GROUP by LP_SAS__c];
  • Then it goes as follows

for (AggregateResult ar : asgns) {           
            updateSass.add(new LP_SAS__c(Id = (ID)ar.get('LP_SAS__c'), Exposure_Total__c = (Decimal)ar.get('total')));
            system.debug('ar ' + ar);
        }
        
        if (updateSass.Size()>0){
            update updateSass;
        }
        
for (AggregateResult ares : asgnsres) {           
            updateSassres.add(new LP_SAS__c(Id = (ID)ares.get('LP_SAS__c'), Reserve_ASGN__c = (Decimal)ares.get('total'),                                         Unearned_Discount_ASGN__c = (Decimal)ares.get('total1'), Exposure_Total__c = (Decimal)ares.get('total2')));
         
        }
        
        if (updateSassres.Size()>0){
            update updateSassres;
        }
        
for (AggregateResult bb : asgnsbb) {
            updateSassbb.add(new LP_SAS__c(Id = (id)bb.get('LP_SAS__c'), Reserve_Buyback__c = (Decimal)bb.get('total'),                                             Unearned_Discount_Buyback__c = (Decimal)bb.get('total1'), Exposure_Total__c = (Decimal)bb.get('total2')));
        }
        
        if (updateSassbb.Size()>0){
            update updateSassbb;
        }
        
for (AggregateResult wo : asgnswo) {
            updateSasswo.add(new LP_SAS__c(Id = (id)wo.get('LP_SAS__c'), Reserve_Write_off__c = (Decimal)wo.get('total'),                                           Unearned_Discount_Write_off__c = (Decimal)wo.get('total1'), Exposure_Total__c = (Decimal)wo.get('total2')));         
        }
        
        if (updateSasswo.Size()>0){
            update updateSasswo;
        }


 
pconpcon
You may be able to condense this down to a single one by saying
 
List<AggregateResult> asgnswo = [
    select LP_SAS__c,
        Status__c,
        sum(Amount_Deducted_from_Reserve__c) total,
        sum(Buyback_DE__c) total1,
        sum(Exposure_Amt__c) total2
    from CC_ASGN__c
    where LP_SAS__c in :sasIds
    group by LP_SAS__c,
        Status__c
];

But I cannot remember off the top of my head if you can do multiple groups by parameters.  If you cannot, then you are stuck doing this in 4 seperate queries.  You may be able to do this in a report and then load the report into your class, but I think that would be slower and more prone to unexpected breakage.
 
JAY_PJAY_P
The best way to avoid so many query is first you can create custom setting for that list or you can try to write test class to cover up all code which you written so you cant hit the govnor limit .
Thank You
Jay