perform Aggregates in repeat, sub query?

["Man"ish]

Hi All,

I am using a sub query to retrieve Opportunity and Contract__c custom object records in a single query

Name field from Opportunity

Name and BPC fields from Contract

Apex Class:

public list<Opportunity> getopps()
   {
      return [Select Name,(Select Name, BPC_Total_Value__c, BPC_Monthly_Value__c, BPC_System_Calculated_Credit_Limit__c  from Contract__r LIMIT 1) from Opportunity where AccountId=:Acc.Id ];
   }

And I display the results in VF page. 

VF Code:

<table>
      <apex:repeat value="{!opps}" var="opp">
        <tr>
          <td><b><apex:outputText value="{!opp.Name}"/></b></td>
          <tr>
          <apex:repeat value="{!opp.Contract__r}" var="contr">
             <td><b>Contract Name </b> <apex:outputText value="{!contr.Name}"/></td>
             
             <td><b>BPC Total Value </b> <apex:outputText value="{!contr.BPC_Total_Value__c}"/></td>
             <td><b>BPC Monthly Value </b><apex:outputText value="{!contr.BPC_Monthly_Value__c}"/></td>                
             <td><b>BPC System Calculated Credit limit</b><apex:outputText value="{!contr.BPC_System_Calculated_Credit_Limit__c}"/></td>
             
             </apex:repeat>
          </tr>
        </tr>
      </apex:repeat>
    </table>

The output result is in the below format: (2 opportunities, with a contract each)

test opportunity1
Contract Name C12345	BPC Total Value 25000000	BPC Monthly Value 1666667	BPC System Calculated Credit limit20000000
test opportunity2
Contract Name C0001	BPC Total Value 100000	BPC Monthly Value 8333	BPC System Calculated Credit limit100000

Now, I want to add another row, where it would display the sum of all the BPC Total Values, BPC Monthly values, BPC Credit Limits.

I need to add all the totals and display.  How do I do it ? I am out of ideas.

Help!

[Andrew Wilkins…]

This is just an idea. I had a requirement similar to this one with a pageblocktable so the code was a little different but the same priniciple applies. In your controller sum the values you want into three variables BPCTotalValuesSum, BPCMonthlyvaluesSum, BPCCreditLimitsSum. You could do a for loop for the results. Then in your visualforce page add another tr outside of the repeat tags. The final tr would display these values from your controller.

["Man"ish]

Ok. Thanks for the input. Will work on these lines. Will keep you posted. Thanks. Happy weekend.

["Man"ish · Accepted solution]

We need not add another tr. I figured it out.

There is something called GROUP BY ROLLUP(Name) to be used in the SOQL Query which gives the aggregates as the last row.