+ Start a Discussion
jai.sjai.s 

How to get sum of columns values of SOQL query data in a table?

Hi,

I have input search field in vf page, as per the search given in that field i am displaying resulted data in a table (Item and price).

now, i have to show sum of price column values in a table(Soql query resulted data as per value given in a input search filed).

Kindly help to achieve this scenario.

Item         Price 
  A            100
  B            300
  C            500
      Total:  900

Thanks in Advance!!

 
KaranrajKaranraj
You can use aggegrate SOQL query to find sum of the column
AggregateResult[] groupedResults
  = [SELECT SUM(Amount)aver FROM Opportunity];
Object sumAmount = groupedResults[0].get('aver');

Then display the "sumAmount" variable in the visualforce page. Check here for more details http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
 
koteswaran shanmugam 5koteswaran shanmugam 5
Hi Karan,

I have tried below approach and i am unable to get the grand total for the column level values while i am taking the functionality checkbox selected for any or few of the row level records selected:-

vf
--
<apex:page controller="ProductDtlsCntl" sidebar="false">
    <script type="text/javascript">
    function selectAllCheckboxes(obj,receivedInputID){
        var inputCheckBox = document.getElementsByTagName("input");
        for(var i=0; i<inputCheckBox.length; i++){
            if(inputCheckBox[i].id.indexOf(receivedInputID)!=-1){
                inputCheckBox[i].checked = obj.checked;
            }
       }
    }
    </script>
    <apex:form id="frm">       
        <apex:pageBlock >
            <apex:pageBlockButtons location="bottom">
                <apex:commandButton value="Search" action="{!searchprts}" reRender="product-table" />
                <apex:commandButton value="Save"  action="{!processSelected}" reRender="product-table"/>
            </apex:pageBlockButtons>
            <apex:pageBlockSection id="product-table" columns="1">
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Name" />
                    <apex:inputText value="{!name}" />
                </apex:pageBlockSectionItem>
                <apex:pageBlockTable value="{!wrapProductList}" var="prdlst" rendered="{!showsearchresults}">
                    <apex:column >
                        <apex:facet name="header">
                            <apex:inputCheckbox id="chkbox" onclick="selectAllCheckboxes(this,'inputId')"/>
                        </apex:facet>
                        <apex:inputCheckbox id="inputId" value="{!prdlst.selected}" />
                    </apex:column>
                    <apex:column >
                        <apex:facet name="header">Name</apex:facet>
                        <apex:outputText id="name" value="{!prdlst.prd.Name}" />
                    </apex:column>
                    <apex:column >
                        <apex:facet name="header">Product Price Unit </apex:facet>
                        <apex:outputText id="id1" value="{!prdlst.prd.Product_Price_Unit__c}" />
                    </apex:column>
                    <apex:column >
                        <apex:facet name="header">Quantity</apex:facet>
                        <apex:inputField id="id2" value="{!prdlst.prd.Quantity__c}" >
                            <apex:actionSupport event="onchange" action="{!onFieldChange}" reRender="frm"/>
                        </apex:inputField>
                    </apex:column>
                    <apex:column >
                        <apex:facet name="header">Total </apex:facet>
                        <apex:variable var="totalPrice" value="{!prdlst.prd.Product_Price_Unit__c*prdlst.prd.Quantity__c}" />
                        <apex:outputText value="{!totalPrice}" />
                    </apex:column>
                </apex:pageBlockTable>
                <apex:outputLabel >Grand Total =
                    <apex:outputText value="{!GrandTotal}" id="value"/>
                </apex:outputLabel>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Apex:-
----
public class ProductDtlsCntl {
    // public List<Product2> Products { get; set; }
    public List<wrpProduct> wrapProductList {get; set;}
    public String name { get; set; }
    public decimal totalPrice{get;set;}
    public decimal GrandTotal {get; set;}
    public decimal total = 0;
    public List<Product2> selectedProducts{get;set;}
    public boolean showsearchresults {get; private set;}

    
    public ProductDtlsCntl()
    {
        wrapProductList = new List<wrpProduct>();
        showsearchresults = false;
        name = '';
        GrandTotal=0;
    }
    public pageReference searchprts()
    {
        if(!(String.isBlank(name))) {
            String srchSyl = '%'+name+'%';
            for(Product2 p : [select Id,Name,Quantity__c,Product_Price_Unit__c,Type__c,Agreement__r.Type__c from Product2 where Name like : srchSyl]){
                wrapProductList.add(new wrpProduct(p));
                showSearchResults= !wrapProductList.isEmpty();
            }
          return null;
        }else{
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Please enter atleast 3 characters'));
            showSearchResults=false;
            return null;
        }   
        
    }
    public Integer onFieldChange(){
        
        return null;
    }
    
    public void processSelected(){
        selectedProducts = new List<Product2>();
        
        for(wrpProduct wrapProductObj :wrapProductList){
            if(wrapProductObj.selected == true) {
                selectedProducts.add(wrapProductObj.prd);
                
            }
        }
        
    }
    public class wrpProduct {
        public Product2 prd {get; set;}
        public Boolean selected {get; set;}
        
        public wrpProduct(Product2 p) {
            prd = p;
            selected = false;
        }
    }
}

Any help of how to calculate the grand total and total using wrapper class?