AggregateResult Grand Total on VF Page

Question

I have a custom VF controller that uses the AggregateResult method to summarize some fields by year. All that works as expected, but now I want to also pull the total for each metric field for all the years combined, a grand total. I'm not sure how to go about this. Any suggestions?

Controller:

public class CalculateQLISummary{
    String quoteId;
    public list<Quote__c> listQLI{get;set;} 

//These properties are used in the VF page to conditionally hide and show columns based on whether they have data

    public CalculateQLISummary(ApexPages.StandardSetController controller) {
        quoteId = ApexPages.currentPage().getParameters().get('id');
    }

    public void init(){

//Used to show the Quote products on the VF page
        listQLI = new list<Quote__c>();
        listQLI = [SELECT Opportunity__r.Id, Must_Be_Checked__c, Name, Quote_Code__c, Contract_Year__c, List_Price__c, 
                   Final_Price__c, Unweighted_Amount__c, Discount__c,QLI_Impact__c, Size__c, Number_of_Users__c,
                   Number_of_Transactions__c, Cyber_Size__c, Uplift__c
                   FROM Quote__c
                   WHERE Opportunity__r.Id =:quoteId AND Must_Be_Checked__c=FALSE];


    public List<AggregateResult> QuoteTotal{
        get{
            List<AggregateResult> qt = [SELECT Opportunity__r.Id, Contract_Year__c cy, sum(List_Price__c) lp, sum(Final_Price__c) fp, sum(Unweighted_Amount__c) uw
                                        FROM Quote__c
                                        WHERE Opportunity__r.Id =:quoteId
                                        GROUP BY Contract_Year__c, Opportunity__r.Id];
            return qt;
            }
        set;
    }          

    public PageReference Cancel(){
        PageReference pg = new PageReference('/' + quoteId);
        return pg;
    }
}

Visualforce Page:

<apex:page StandardController="Quote__c" recordSetVar="quotes" Extensions="CalculateQLISummary" action="{!init}" tabStyle="Quote__c">
    <apex:pageMessages id="pgMess" />

    <style type="text/css">
        .SectHeader {
            font-weight: bold;
            font-size:10pt;
            background: #288fb0;
            color: white;
            width:100%;
            border:1px solid black;
            padding:8px;
            border-radius:5px;
            }
        .col{
            text-align:center;
            }
        .txt{
            text-align:center;
            }
    </style>

    <apex:form id="frmReview" > 
        <apex:pageBlock id="pgBlckQSummary" title="Quote Line Summary">
            <center><apex:commandButton title="Back to Quote" value="Back to Quote" action="{!cancel}"/></center><br/><br/>

        <apex:outputText value="AGGREGATE AMOUNTS" styleClass="SectHeader"/><p/>
            <apex:pageBlockTable title="Summary" value="{!QuoteTotal}" var="quoteTot" style="width:100%">

                <apex:column headerValue="Contract Year" styleClass="txt" headerClass="col">
                    {!quoteTot['cy']}
                </apex:column>
                <apex:column headerValue="List Price" styleClass="txt" headerClass="col">
                    {!quoteTot['lp']}
                </apex:column>
                <apex:column headerValue="Final Price" styleClass="txt" headerClass="col">
                    {!quoteTot['fp']}
                </apex:column>
                <apex:column headerValue="Unweighted Amount" styleClass="txt" headerClass="col">
                    {!quoteTot['uw']}
                </apex:column>
            </apex:pageBlockTable>
<br/><br/><Br/>

        <apex:outputText value="Summary of Products" styleClass="SectHeader"/><p/>
            <apex:pageBlockTable title="Quote Summary" value="{!listQLI}" var="OLI" style="width:100%">

                <apex:column headerValue="Product" style="width:15%" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Name}"/>
                </apex:column>
                <apex:column headerValue="Product Code" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Quote_Code__c}"/>
                </apex:column>
                <apex:column headerValue="Contract Year" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Contract_Year__c}"/>
                </apex:column>
                <apex:column headerValue="RMS List Price" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.List_Price__c}"/>
                </apex:column>
                <apex:column headerValue="Client Amount" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Final_Price__c}"/>
                </apex:column>
                <apex:column headerValue="Unweighted Amount" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Unweighted_Amount__c}"/>
                </apex:column>
                <apex:column headerValue="Discount From List %" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Discount__c}"/>
                </apex:column>
                <apex:column headerValue="Uplift %" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Uplift__c}"/>
                </apex:column>
                <apex:column headerValue="QLI Imact" styleClass="txt" headerClass="col" rendered="{!QLI_Impact}">
                    <apex:outputField value="{!OLI.QLI_Impact__c}"/>
                </apex:column>
                <apex:column headerValue="Size" styleClass="txt" headerClass="col">
                    <apex:outputField value="{!OLI.Size__c}"/>
                </apex:column>
                <apex:column headerValue="Number of Users" styleClass="txt" headerClass="col" rendered="{!Num_User}">
                    <apex:outputField Value="{!OLI.Number_of_Users__c}"/>
                </apex:column>
                <apex:column headerValue="Number of Transactions" styleClass="txt" headerClass="col" rendered="{!Num_Trans}">
                    <apex:outputField Value="{!OLI.Number_of_Transactions__c}"/>
                </apex:column>
                <apex:column headerValue="Cyber Size" styleClass="txt" headerClass="col" rendered="{!Cyb_Siz}">
                    <apex:outputField value="{!OLI.Cyber_Size__c}"/>
                </apex:column>
            </apex:pageBlockTable>    

        </apex:pageBlock>
    </apex:form>
</apex:page>

Answer 1

Aggregates will work like a map, so when displaying map values on VF Pages you should get your values by the key for the map. So you would access your index like you're getting the key from it's variable.

Given your supplied controller code:

<apex:repeat value="{!QuoteTotal}" var="ar" >
    <apex:outputText value="{!ar[ 'lp' ]}" />
    <apex:outputText value="{!ar[ 'fp' ]}" />
    <apex:outputText value="{!ar[ 'uw' ]}" />
</apex:repeat>

Your key in this case will be the alias for your aggregate.